current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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.

share|improve this question
1  
You should add the query you had, even if it's not working - it may be part of a solution. –  Erwin Brandstetter Mar 18 at 3:50

1 Answer 1

up vote 3 down vote accepted

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

  • Process jsonb with a complex nested structure.
  • Run the equivalent of a relational division query on the document 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"

We only need the id, so don't include the name. Per documentation:

JSON fields that do not appear in the target row type will be omitted from the 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 (pg 9.4 not available yet).

Explain

  • 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:

    , 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á.

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.

share|improve this answer
    
That looks very promising! Thank you for your in-depth answer. I'll check it out later tonight and let you know, but the SQL Fiddle (even though it's 9.3) looks very good. –  Timo Mar 19 at 8:03
    
Works great. Thank you. –  Timo Mar 21 at 21:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.