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

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

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

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 samealbum, src, and background

：

SELECt array_agg(r.rep_id) AS ids, count(*) AS ctFROM   reports r     , json_populate_recordset(null::obj, r.data->'objects') oGROUP  BY r.data->>'background'        , o.album        , o.scrORDER  BY count(*) DESCLIMIT  3;

无论是否来自同一行，每个对象都很重要。您没有定义如何正确处理。因此，

rep_id

可以在数组中多次d出

ids

。添加

DISTINCT

到

array_agg()

折叠可能的重复项。在这种情况下，计数

ct

可以大于数组的长度

ids

。

需要JSON函数和运算符以及隐式的

JOINLATERAL

Postgres 9.3 。

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

json_array_elements()

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

SELECt array_agg(r.rep_id) AS ids, count(*) AS ctFROM   reports r     , **json_array_elements(r.data- >'objects')** oGROUP  BY r.data->>'background'        , **o- >>'album'**        , **o- >>'scr**'ORDER  BY count(*) DESCLIMIT  3;