使用JSON数据类型的记录嵌套数组查询组合

一尘不染

使用JSON数据类型的记录嵌套数组查询组合

json

我正在开发一个使用Postgres
JSON数据类型的Rails应用程序。我在名为data的表中有一个JSON列reports。假设我有多个这样的条目：

Entry 1: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barB.png", "pos": "top"}],   "background":"background.png"}
Entry 2: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barC.png", "pos": "top"}],   "background":"bacakground.png"}
Entry 3: {"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}
Entry 4: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 3, "src":"barB.png", "pos": "top"}],   "background":"backgroundA.png"}

我想做的是返回具有相同唱片集，src和背景的条目的不同组合（注意：在objects节点内，数组元素的顺序无关紧要）。例如，查询应将条目1,3作为一组进行匹配，将条目2与另一组进行匹配，依此类推。目标是找到前3个最常见的组合。我知道如何使用Ruby来执行此操作，但是我必须查询大量条目，然后遍历所有条目。如果Postgres可以处理此任务，则似乎效率更高。我还不足以让SQL专家知道这是否可行。

这是我想要的结果。在其中objects，条目1和3都包含{"album": 1, "src":"fooA.png"}, {"album": 2, "src":"barB.png"}，并且都具有匹配项backgrounds。我想将它们组合为一个2的组合。

由于条目2在此条件下不匹配任何条目，因此它是计数为1的另一个组合。条目4也被认为是计数为1的另一个组合。因此，我得到的结果是：

ids  |  count
--------------
1,3  | 2
2    | 1
4    | 1

要么

combinations                                                                                                                               | count
---------------------------------------------------------------------------------------------------------------------------------------------------
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},  {"album": 2, "src":"barB.png", "pos": "top"}],  "background":"background.png"}  | 2
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},  {"album": 2, "src":"barC.png", "pos": "top"}],  "background":"bacakground.png"} | 1
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},  {"album": 3, "src":"barB.png", "pos": "top"}],  "background":"backgroundA.png"} | 1

以较容易实现的为准。

在我的实际数据中，我在节点内的JSON数组中具有除just
album和以外的其他值。您会注意到，我已经包括了此案例。我只关心使用，和值相匹配的连击。我希望忽略任何其他值。src``objects``pos``album``src``background

注意

当我测试Erwin的解决方案时，我不断收到此错误，我知道为什么：

ERROR:  cannot call json_populate_recordset on a nested object

我的json值实际上要复杂一些。例如：

{"objects":[{"album": 1, "src":"fooA.png", "pos": "top", filters: []},  {"album": 2, "src":"barB.png", "pos": "top", filters: []}

显然，filters是一个嵌套对象，不受的支持json_populate_recordset。但是，如果没有简单的选择，我想我可以解决此问题。同样，我认为这是可能的吗？

更新

由于上述示例数据中有错别字（这是我的错），因此该解决方案有点不完整。修正错字时，解决方案不起作用。在这里找到这种情况的答案。但是，Erwin的解决方案仍然可以解决与上述情况类似的情况。

阅读 337

2020-07-27

共1个答案

一尘不染

给定此表（您应该以这样的形式提供）：

CREATE TABLE reports (rep_id int primary key, data json);
INSERT INTO reports (rep_id, data)
VALUES 
  (1, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barB.png", "pos": "top"}],   "background":"background.png"}')
, (2, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barC.png", "pos": "top"}],   "background":"bacakground.png"}')
, (3, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}')
, (4, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 3, "src":"barB.png", "pos": "top"}],   "background":"backgroundA.png"}')
;

众所周知的可翻译类型的JSON记录

使用
json_populate_recordset()了unnesting记录"objects"。该函数需要注册的行类型来定义结果列的名称和数据类型。出于本演示的目的或通常用于即席查询的目的，以以下内容建模的
临时表"objects"提供了相同的内容：

CREATE TEMP TABLE obj(album int, src text, pos text);

找到the top 3 most common combinations… of entries that have the same album, src, and background：

SELECT array_agg(r.rep_id) AS ids, count(*) AS ct
FROM   reports r
     , json_populate_recordset(null::obj, r.data->'objects') o
GROUP  BY r.data->>'background'
        , o.album
        , o.scr
ORDER  BY count(*) DESC
LIMIT  3;

无论是否来自同一行，每个对象都很重要。您没有定义如何正确处理。因此，rep_id可以在数组中多次弹出ids。添加DISTINCT到array_agg()折叠可能的重复项。在这种情况下，计数ct可以大于数组的长度ids。

需要JSON函数和运算符以及隐式的JOIN LATERAL
Postgres 9.3 。

未知或不可翻译类型的JSON记录

json_array_elements()只是取消嵌套json数组，而无需将结果转换为SQL行。相应地使用JSON运算符访问各个字段。

SELECT array_agg(r.rep_id) AS ids, count(*) AS ct
FROM   reports r
     , **json_array_elements(r.data- >'objects')** o
GROUP  BY r.data->>'background'
        , **o- >>'album'**
        , **o- >>'scr**'
ORDER  BY count(*) DESC
LIMIT  3;

2020-07-27