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.
The table contains two rows with the following json
data:
First row:
{
"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"
}
]
}
}
second row:
{
"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"
}
]
}
}
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.
jsonb
with a complex nested structure.First jsonb_populate_recordset()
works with a registered row type. Can be the row type of any (temp) table or view, or a composite type explicitly created with CREATE TYPE
. If there is none, register one. For ad-hoc use, a temporary type does the job (undocumented hack, dropped automatically at the end of the session):
CREATE TYPE pg_temp.foo AS (id int); -- just "id"
We only need id
, so don't include name
. The manual:
JSON fields that do not appear in the target row type will be omitted from the output
If you need it fast, create a GIN index on the jsonb
column. The more specialized operator class jsonb_path_ops
is even faster than the default jsonb_ops
:
CREATE INDEX teams_json_gin_idx ON teams USING GIN (json jsonb_path_ops);
Can be used by the "contains" operator @>
:
SELECT t.json->>'id' AS team_id
, ARRAY (SELECT * FROM jsonb_populate_recordset(null::foo, t.json#>'{members,players}')) AS players
FROM teams t
WHERE json @> '{"members":{"players":[{"id":3},{"id":4},{"id":7}]}}';
SQL/JSON path language in Postgres 12+ can use the same index:
SELECT t.json->>'id' AS team_id
, ARRAY (SELECT * FROM jsonb_populate_recordset(null::foo, t.json#>'{members,players}')) AS players
FROM teams t
WHERE json @? '$.members ? (@.players.id == 3) ? (@.players.id == 4) ? (@.players.id == 7)';
db<>fiddle here
See:
Without index support - unless you create a tailored expression index, see below.
SELECT t.json->>'id' AS team_id, p.players
FROM teams t
JOIN LATERAL (
SELECT ARRAY (
SELECT * FROM jsonb_populate_recordset(null::foo, t.json#>'{members,players}')
)
) AS p(players) ON p.players @> '{3,4,7}';
Extracts the JSON array with player records:
t.json#>'{members,players}'
From these, I unnest rows with just the id
with:
jsonb_populate_recordset(null::foo, t.json#>'{members,players}')
... 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:
, JOIN LATERAL (SELECT ... ) AS p(players) ...
Immediately filter the resulting arrays in the join condition to keep only the ones we are looking for - with the "contains" array operator @>
:
... ON p.players @> '{3,4,7}'
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:
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.