Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE teams (json jsonb);
CREATE TABLE foo(id int);

INSERT INTO teams VALUES
('{
    "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"
            }
        ]
    }
}');

CREATE TABLE



CREATE TABLE



INSERT 0 2


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


team_id
players


1
{2,3,4,6,7}



SELECT 1


-- Faster with GIN index:
CREATE INDEX teams_json_gin_idx ON teams USING GIN (json jsonb_path_ops);

CREATE INDEX


-- Using "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}]}}';


team_id
players


1
{2,3,4,6,7}



SELECT 1


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


team_id
players


1
{2,3,4,6,7}



SELECT 1