数组中嵌套对象的jsonb查询 [英] jsonb query with nested objects in an array
问题描述
我正在使用PostgreSQL 9.4，其中表teams包含名为json的jsonb列.我正在寻找一个查询，我可以让所有拥有在其球员数组中的球员3，4和7的球队.
I'm using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players.
该表包含两行，其中包含以下json数据:
The table contains two rows with the following json data:
第一行:
{
"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,
"name": "A dude"
},
"players": [
{
"id": 3,
"name": "C dude"
},
{
"id": 5,
"name": "E dude"
},
{
"id": 6,
"name": "F dude"
},
{
"id": 7,
"name": "G dude"
},
{
"id": 8,
"name": "H dude"
}
]
}
}
该查询看起来如何才能获得所需的球队名单?我尝试了一个查询，在该查询中我将根据成员玩家jsonb_array_elements(json -> 'members' -> 'players')->'id'创建一个数组并进行比较，但我能够完成的结果是，某个团队中有任何被比较的玩家ID可用，而不是全部他们.
How does the query have to look like to get the desired list of teams? I've tried a query where I'd create an array from the member players jsonb_array_elements(json -> 'members' -> 'players')->'id' and compare them, but all I was able to accomplish is a result where any of the compared player ids was available in a team, not all of them.
推荐答案
您一次要面对两项艰巨的任务.我很感兴趣.
You are facing two non-trivial tasks at once. I am intrigued.
- 处理具有复杂嵌套结构的
jsonb. - 对文档类型运行等效的关系除法查询.
首先，为jsonb_populate_recordset()注册行类型.您可以使用CREATE TYPE永久创建类型，也可以创建临时使用的临时表(在会话结束时自动删除):
First, register a row type for jsonb_populate_recordset(). You can either create a type permanently with CREATE TYPE, or create a temp table for ad-hoc use (dropped automatically at the end of the session):
CREATE TEMP TABLE foo(id int); -- just "id", we don't need "name"
我们只需要id，所以不要包括name. 每个文档:
We only need the id, so don't include the name. Per documentation:
目标行类型中未出现的JSON字段将从输出中省略
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}';
Postgres 9.3中json的
SQL提琴 (第9.4页尚不可用).
SQL Fiddle for json in Postgres 9.3 (pg 9.4 not available yet).
-
使用播放器记录提取JSON数组:
Extracts the JSON array with player records:
t.json#>'{members,players}'
通过这些，我只将id与以下行嵌套在一起:
From these, I unnest rows with just the id with:
jsonb_populate_recordset(null::foo, t.json#>'{members,players}')
...，然后将它们立即聚合到Postgres数组中，因此我们在基表中每行保留一行:
... and immediately aggregate those into a Postgres array, so we keep one row per row in the base table:
SELECT ARRAY ( ... )
所有这些都发生在横向连接中:
All of this happens in a lateral join:
, LATERAL (SELECT ... ) AS p(players)
使用包含" 数组运算符@> :
Immediately filter the resulting arrays to keep only the ones we are looking for - with the "contains" array operator @>:
WHERE p.players @> '{3,4,7}'
Voilá.
如果您在一个大表上多次运行此查询，则可以创建一个伪造的IMMUTABLE函数，该函数提取上述数组，并基于此函数创建函数 GIN索引以使其超级快.
伪造"是因为该函数取决于基础行类型，即依赖于目录查找，并且如果改变则将改变. (因此，请确保它不会改变.)与此类似:
If you run this query a lot on a big table, you could create a fake IMMUTABLE function that extracts the array like above and create functional GIN index based on this function to make this super fast.
"Fake" because the function depends on the underlying row type, i.e. on a catalog lookup, and would change if that changes. (So make sure it does not change.) Similar to this one:
在旁边:
不要使用像json这样的类型名称作为列名称(即使允许)，这会引起棘手的语法错误和令人困惑的错误消息.
Aside:
Don't use type names like json as column names (even if that's allowed), that invites tricky syntax errors and confusing error messages.
这篇关于数组中嵌套对象的jsonb查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
