JSONB-запрос с вложенными объектами в массиве

Я использую PostgreSQL 9.4 с таблицей teams содержащий jsonb колонка с именем json, Я ищу запрос, где я могу получить все команды, в которых есть игроки 3, 4 а также 7 в их массиве игроков.

Таблица содержит две строки со следующими json данные:

Первый ряд:

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

Как должен выглядеть запрос, чтобы получить желаемый список команд? Я пробовал запрос, где я бы создал массив из игроков-участников jsonb_array_elements(json -> 'members' -> 'players')->'id' и сравнивать их, но все, что мне удалось сделать, это результат, когда в команде был доступен любой из сравниваемых идентификаторов игроков, а не все.

Источник

3 ответа

Решение

Вы сталкиваетесь с двумя нетривиальными задачами одновременно. Я заинтригован.

  • Процесс jsonb со сложной вложенной структурой.
  • Запустите эквивалент запроса реляционного разделения для типа документа.

Сначала зарегистрируйте тип строки для jsonb_populate_recordset(), Вы можете либо создать тип навсегда с помощью CREATE TYPEили создайте временную таблицу для специального использования (автоматически удаляется в конце сеанса):

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

Нам нужно только idтак что не включайте name, По документации:

Поля JSON, которые не отображаются в целевом типе строки, будут исключены из вывода

запрос

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 для json в Postgres 9.3 (pg 9.4 еще не доступен).

объяснять

  • Извлекает массив JSON с записями игроков:

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

  • Из них я снимаю ряды только с id с:

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

    ... и немедленно объединяем их в массив Postgres, поэтому мы сохраняем одну строку на строку в базовой таблице:

    SELECT ARRAY ( ... )

  • Все это происходит в боковом соединении:

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

  • Немедленно отфильтруйте полученные массивы, чтобы оставить только те, которые мы ищем - с помощью оператора массива "contains"@>:

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

Вуаля.

Если вы выполняете этот запрос много на большом столе, вы можете создать подделку IMMUTABLE функция, которая извлекает массив, как описано выше, и создает функциональный индекс GIN на основе этой функции, чтобы сделать это очень быстро.
"Подделка", потому что функция зависит от базового типа строки, то есть от поиска в каталоге, и может измениться, если это изменится. (Поэтому убедитесь, что он не меняется.) Подобный этому:

В сторону:
Не используйте имена типов, такие как json как имена столбцов (даже если это разрешено), что вызывает сложные синтаксические ошибки и запутанные сообщения об ошибках.

Источник

Я хотел сделать то же самое, что и выше. Единственным условием было сопоставление подстроки, а не точное соответствие.

Это то, что я закончил (вдохновленный ответом выше, конечно)

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

Размещать здесь, если это поможет.

Источник

https://www.postgresql.org/docs/release/14.0/

Подписку теперь можно применять к любому типу данных, для которого это полезная нотация, а не только к массивам. В этом выпуске типы jsonb и hstore получили операторы подписки. Давайте воспользуемся функцией подписки в postgresql 14.

      with a as(
select data['id'] as teamid,
       (jsonb_array_elements( data['members']['players']))['id'] as playerid
from teams), b as( select teamid, array_agg(playerid) as playerids from a group by 1)
select b.* from b where b.playerids @> '{3,4,7}';

возвращает:

       teamid |  playerids
--------+-------------
 1      | {2,3,4,6,7}

Скрипка БД

Источник
Другие вопросы по тегам