Geek Answers Handbook

Jsonb request with nested objects in an array

I am using PostgreSQL 9.4 with a teams table containing a jsonb column named json . I'm looking for a query where I can get all the teams that have players 3 , 4 and 7 in their array of players.

The table contains two rows with the following json data:

First line:

 { "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 line:

 { "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" } ] } }

What should the query look like to get the desired list of commands? I tried a request where I would create an array of participating members jsonb_array_elements(json -> 'members' -> 'players')->'id' and compared them, but all I could do was result when any of player identifiers being compared was available in the team, and not in all of them.

+8
sql postgresql relational-division jsonb
source share
2 answers

You are faced with two non-trivial tasks at once. I am intrigued.

  • A jsonb process with a complex nested structure.
  • Execute the equivalent of a relational division query into a document type.

First register the row type for jsonb_populate_recordset() . You can either create the type permanently using CREATE TYPE , or create a temporary table to use ad-hoc (automatically discarded at the end of the session):

 CREATE TEMP TABLE foo(id int); -- just "id", we don't need "name"

We only need id , so do not include name . In the documentation:

JSON fields that are not displayed in the target string type will not be output

Query

 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}';

SQL Fiddle for json in Postgres 9.3 (p. 9.4 not yet available).

To explain

  • Retrieves a JSON array with player entries:

     t.json#>'{members,players}'

  • From this, I ignore strings only with id with:

     jsonb_populate_recordset(null::foo, t.json#>'{members,players}')

    ... and immediately aggregate them into a Postgres array, so we save one row per row in the base table:

     SELECT ARRAY ( ... )

  • All this happens in a lateral connection:

     , LATERAL (SELECT ... ) AS p(players)

  • Filter the resulting arrays immediately to save only the ones we are looking for - with the "contains" array operator @> :

     WHERE p.players @> '{3,4,7}'

Voila.

If you run this query a lot on a large table, you can create a fake IMMUTABLE function that retrieves the array as described above and creates a functional GIN index based on this function to make this super fast.
"Fake" because the function depends on the base type of the string, that is, on a directory search, and will change if that changes. (Therefore, make sure it has not changed.) Similarly:

  • Pointer to search for an item in a JSON array

Besides:
Do not use type names such as json as column names (even if resolved), which causes complex syntax errors and confusing error messages.

+12
source share

I wanted to do the same as above. Only another condition was that I had to execute a substring, not an exact match.

This is what I ended up doing (inspired by the answer above, of course)

 SELECT t.json->>'name' AS feature_name, f.features::text FROM teams t , LATERAL ( SELECT * FROM json_populate_recordset(null::foo, t.json#>'{members,features}') ) AS f(features) WHERE f.features LIKE '%dud%';

Spending it here if that helps.

+1
source share

More articles:


All Articles