Запрос для элементов массива внутри типа JSON

Question

Я пытаюсь проверить json введите PostgreSQL 9.3.
у меня есть json колонка называется data в таблице под названием reports, JSON выглядит примерно так:

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

Я хотел бы запросить таблицу для всех отчетов, которые соответствуют значению 'src' в массиве 'objects'. Например, можно ли запросить БД для всех отчетов, которые соответствуют 'src' = 'foo.png'? Я успешно написал запрос, который может соответствовать "background":

SELECT data AS data FROM reports where data->>'background' = 'background.png'

Но с тех пор "objects" имеет массив значений, я не могу написать что-то, что работает. Можно ли запросить БД для всех отчетов, которые соответствуют 'src' = 'foo.png'? Я просмотрел эти источники, но до сих пор не могу понять:

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• Как выполнить запрос с использованием полей внутри нового типа данных PostgreSQL JSON?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

Я также пробовал такие вещи, но безрезультатно:

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

Я не эксперт по SQL, поэтому я не знаю, что я делаю неправильно.

Answer 1

json в Postgres 9.3+

Разверните массив JSON с помощью функции json_array_elements() в боковом соединении в FROM предложение и проверка его элементов:

WITH reports(data) AS (
   VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]
           , "background":"background.png"}'::json)
   ) 
SELECT *
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

CTE (WITH запрос) просто заменяет таблицу reports,
Или, эквивалентный только для одного уровня вложенности:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->> , -> а также #> операторы объяснены в руководстве.

Оба запроса используют неявный JOIN LATERAL,

SQL Fiddle.

Тесно связанный ответ:

• Запрос для элемента массива в столбце JSON

jsonb в Postgres 9,4+

Используйте эквивалент jsonb_array_elements(),

Еще лучше, используйте новый оператор "содержит" @> (лучше всего в сочетании с соответствующим индексом GIN в выражении data->'objects'):

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

С ключом objects содержит массив JSON, нам нужно сопоставить структуру в поисковом запросе и заключить элемент массива в квадратные скобки. Удалите скобки массива при поиске простой записи.

Подробное объяснение и дополнительные параметры:

• Индекс для поиска элемента в массиве JSON

Answer 2

Создайте таблицу со столбцом типа json

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

Теперь давайте вставим данные JSON

# INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}');

# INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}');

Теперь давайте сделаем несколько запросов для получения данных

# select data->'name' from friends;

# select data->'name' as name, data->'work' as work from friends;

Вы могли заметить, что результаты поставляются с кавычками ( ") и скобками ([ ])

    name    |            work            
------------+----------------------------
 "Arya"     | ["Improvements", "Office"]
 "Tim Cook" | ["Cook", "ceo", "Play"]
(2 rows)

Теперь для получения только значений просто используйте ->>

# select data->>'name' as name, data->'work'->>0 as work from friends;

#select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';

Answer 3

Выберите data->'objects'->0->'src' как SRC из таблицы, где data->'objects'->0->'src' = 'foo.png'