jsonb query with nested objects in an array

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.

• Process jsonb with a complex nested structure.
• Run the equivalent of a relational division query on the document type.

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

Query with index support

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:

• Find rows containing a key in a JSONB array of records
• Update all values for given key nested in JSON array of objects

Simple query

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

db<>fiddle here
_{Old sqlfiddle}

How?

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:

• Index for finding an element in a JSON array

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.