给定此表（您应该以这样的形式提供）：
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可以在数组中多次弹出
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;
