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' и сравнил их, но все, что я смог достичь, это результат, когда любой из сравниваемых идентификаторов игроков был доступен в команде, а не все из них.


postgresql sql jsonb postgresql-9.4 relational-division
person Timo    schedule 17.03.2015    source источник
comment
Вам следует добавить запрос, который у вас был, даже если он не работает - он может быть частью решения.   -  person Erwin Brandstetter    schedule 18.03.2015

Ответы (2)


arrow_upward
15
arrow_downward

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

  • Процесс 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 (стр. 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)

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

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

Вуаля.

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

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

person Erwin Brandstetter    schedule 18.03.2015
comment
Это выглядит очень многообещающе! Спасибо за подробный ответ. Я проверю его сегодня вечером и дам вам знать, но SQL Fiddle (даже несмотря на то, что он 9.3) выглядит очень хорошо. - person Timo; 19.03.2015
comment
Работает отлично. Спасибо. - person Timo; 22.03.2015
comment
@Erwin Brandstetter У меня здесь очень похожий вопрос stackoverflow.com/questions/44711199/ Если вы можете, ответьте то же самое. Мы будем очень признательны. Спасибо! - person suprita shankar; 23.06.2017
comment
Разобрался и опубликовал ответ. - person suprita shankar; 23.06.2017

arrow_upward
1
arrow_downward

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

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

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

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

person suprita shankar    schedule 23.06.2017
comment
Спасибо, что поделился. - person Timo; 23.06.2017
comment
Рассмотрите добавленные более простые альтернативы по вашему соответствующему вопросу. - person Erwin Brandstetter; 23.06.2017