我正在使用Postgresql 9.4和一个包含名为json的jsonb列的表团队.我正在寻找一个查询,我可以让所有队员在他们的球员阵列中拥有3号,4号和7号球员.
该表包含两行,包含以下json数据：
第一排：
{
"id": 1,"name": "foobar","members": {
"coach": {
"id": 1,"name": "A dude"
},"players": [
{
"id": 2,"name": "B dude"
},{
"id": 3,"name": "C dude"
},{
"id": 4,"name": "D dude"
},{
"id": 6,"name": "F dude"
},{
"id": 7,"name": "G dude"
}
]
}
}
第二排：
{
"id": 2,"name": "bazbar","members": {
"coach": {
"id": 11,"players": [
{
"id": 3,{
"id": 5,"name": "E dude"
},"name": "G dude"
},{
"id": 8,"name": "H dude"
}
]
}
}
查询如何获得所需的团队列表？我已经尝试了查询,我会创建成员玩家jsonb_array_elements阵列(JSON – >“成员” – >“玩家”) – >“ID”并加以比较,但所有我能做到结果是任何比较的玩家ID在团队中都可用,而不是全部.
解决方法
你一次面临两个非平凡的任务.我很好奇.
>使用复杂的嵌套结构处理jsonb.
>在文档类型上运行等效的关系除法查询.
首先,为jsonb_populate_recordset()注册一个行类型.您可以使用CREATE TYPE永久创建类型,也可以创建临时表以供临时使用(在会话结束时自动删除)：
CREATE TEMP TABLE foo(id int); -- just "id",we don't need "name"
我们只需要id,所以不要包含名称. Per documentation:
JSON fields that do not appear in the target row type will be omitted from the output
询问
SELECT t.json->>'id' AS team_id,p.players
FROM teams t,LATERAL (SELECT ARRAY (
SELECT * FROM jsonb_populate_recordset(null::foo,t.json#>'{members,players}')
)
) AS p(players)
WHERE p.players @> '{3,4,7}';
07.0对于Postgres 9.3中的json(第9.4页尚未提供).
说明
>使用玩家记录提取JSON数组：
t.json#>'{members,players}'
jsonb_populate_recordset(null::foo,players}')
…并立即将它们聚合成Postgres数组,因此我们在基表中每行保留一行：
SELECT ARRAY ( ... )
>所有这些都发生在横向连接中：
,LATERAL (SELECT ... ) AS p(players)
>立即过滤生成的数组,只保留我们要查找的数组 – 使用“contains” array operator @>：
WHERE p.players @> '{3,7}'
瞧.
如果你在一个大表上大量运行这个查询,你可以创建一个假的IMMUTABLE函数,它像上面一样提取数组,并根据这个函数创建函数GIN索引,使这个超快.
“假”因为函数取决于基础行类型,即在目录查找上,并且如果更改则会更改. (所以要确保它不会改变.)与此类似：
