Как запросить глубоко вложенные данные JSON в PSQL

Недавно мне пришлось написать скрипт, который должен был изменить некоторую структуру данных JSON в базе данных PSQL. По сути, мне приходилось анализировать одно значение и помещать его в другое поле. Поскольку у меня не было большого опыта работы с SQL или PSQL в этом отношении, чтение и понимание документации для моих конкретных случаев использования заняло слишком много времени.

У меня был сложный и глубоко вложенный JSON, в котором было не очень понятно, как получить доступ к некоторой информации.

В этой статье я хочу задокументировать некоторые вещи, которые я узнал.

Для начала мне нужно место, где я могу возиться с базами данных, и по счастливой случайности я нашел dbfiddle. Вы можете ознакомиться с запросами из этой статьи здесь.

Сначала давайте начнем с создания таблицы и указания, что нам нужна база данных PSQL. И сразу добавим некоторые данные, с которыми мы сможем работать позже.

CREATE TABLE jsonb_exp (
  id serial primary key,
  data jsonb
);

INSERT INTO jsonb_exp (data) VALUES 
  ('{"data":{"1":{"items":[{"name":"John"},{"name":"Jack"}]},"2":{"items":[{"name":"John Dorian"},{"name":"Jack Sparrow"}]},"3":{"items":[{"name":"Tom Cruise"},{"name":"somerandomtext"}]},"5":{"items":[{"name":"Tom Cruise"}]}},"property":"Some string"}'),
  ('{"data":{"4":{"items":[{"name":"Maria"},{"name":"Jack"}]},"6":{"items":[{"name":"Jack Jackson"},{"name":"Thomas ---"}]},"7":{"items":[{"name":"-"},{"name":"somerandomtext"}]},"15":{"items":[{"name":"hello"}]}},"property":"Some string"}'),
  ('{"a": "b", "c": [1, 2, 3]}'

Обычно у вас не было бы другой структуры внутри вашего JSON для разных записей, но это только для тестов. Теперь мы можем выбрать и посмотреть, что мы создали в формате таблицы.

SELECT * from jsonb_exp;

Во-первых, давайте разберемся с основами.

-> returns jsonb
->> returns text

Не знаете, с каким типом вы работаете, вы можете использовать pg_typeof.

SELECT data->>'data' as data, pg_typeof(data->'data'), pg_typeof(data->>'data') from jsonb_exp where data->>'data' IS NOT NULL;

Запрос из глубоко вложенного JSONB

В этом сценарии, как вы можете видеть в приведенном выше примере: у нас есть объект, он имеет определенную структуру, но некоторые ключи похожи на индексы массива. Не обычное дело, наверное. Но бывает.

На помощь приходит следующий метод:

jsonb_path_query()

Он есть в документации, но мне не сразу было понятно, как он работает. В качестве первого аргумента вы передаете объект, затем вы можете передать запрос. Допустим, нам нужно запросить определенные элементы из объекта, из свойства «элементы» и только те, которые содержат «Джек».

SELECT id, data, item FROM jsonb_exp t, jsonb_path_query(data->'data', '$.*.items[*]') as item
 WHERE data->'data' IS NOT NULL AND item->>'name'='Jack';

Распаковка. Давайте сначала посмотрим на запрос jsonb_path_query, все остальное — это просто поддержка вокруг него, чтобы продемонстрировать, что мы сделали.

Чтение ‘$..items[]’, вот как это можно прочитать. $ — сам объект, он может содержать ключ, которого мы не знаем — помечаем звездочкой. Внутри этого мы хотим найти элементы свойств, которые являются массивом, и с помощью [*] мы говорим, что нам нужен каждый элемент внутри этого массива.

Запятая в запросе — это просто неявное перекрестное соединение, не имеющее отношения к методам JSONB.

Удалите пункт «ГДЕ» и сразу отфильтруйте

Вот код, вы можете добавить условия внутри запроса.

SELECT id, data, jsonb_path_query(data->'data', '$.*.items[*] ? (@.name == $name)', '{"name": "John"}') FROM jsonb_exp
 WHERE data->'data' IS NOT NULL;

Как вы можете видеть здесь, вы даже можете добавить третий параметр, в котором вы можете хранить переменные, которые будут использоваться в запросе. Это просто для демонстрационных целей, вы можете полностью удалить его и сравнить со строкой без использования дополнительных переменных.

Обратите внимание, здесь вы используете двойные знаки равенства, а не один, как за пределами этого запроса.

Вы также не можете просто написать LIKE в качестве оператора сравнения. Но вы можете использовать like_regex. Допустим, мы хотим найти каждый элемент, имя которого начинается с «Джон».

SELECT id, data, jsonb_path_query(data->'data', '$.*.items[*] ? (@.name like_regex "^John")') FROM jsonb_exp
 WHERE data->'data' IS NOT NULL;

Надеюсь, это было полезно.

Главное изображение Люка Чессера на Unsplash