Как получить доступ к вложенному JSON в Postgresql 9.4?

Я пытаюсь получить доступ к вложенному полю jsonb в Postgresql 9.4. Но мне трудно получить запись на основе ее вложенных значений jsonb. Кто-нибудь имел успех в этом?

Сначала я создал таблицу:

CREATE TABLE Meal (
    id INT
  , recipe JSONB
);

Во-вторых, я ввел следующую запись в dbo.Meal: (Я запустил json через jsonlint.com, и он вернулся)

INSERT INTO Meal (id, recipe)
VALUES (
1,
'{
  "meal": [{
  "calories" : 900,
  "serves" : [{"min": 2, "max": 4}],
  "fruit" : [{"id": 1, "qty": 2}, {"id": 4, "qty": 3}],
  "veggie" : [{"id": 4, "qty": 1}, {"id": 2, "qty": 10}]
 }]
}');

В-третьих, я попробовал следующие запросы для извлечения этой записи в зависимости от ее количества калорий (никто из них не работает):

Они возвращают 0 записей:

SELECT * FROM Meal ...

WHERE recipe::json#>>'{meal, calories}' = '900';
WHERE recipe::json->>'{meal, calories}' = '900';
WHERE recipe::json->>'meal[calories]' = '900';
WHERE recipe::json->>'{meal[calories]}' = '900';
WHERE recipe::json#>>'{meal[calories]}' = '900';
WHERE recipe::json#>>'{meal.calories}' = '900';
WHERE recipe::json->>'{meal.calories}' = '900';

WHERE recipe::jsonb#>>'{meal, calories}' = '900';
WHERE recipe::jsonb->>'{meal, calories}' = '900';
WHERE recipe::jsonb#>>'{meal[calories]}' = '900';
WHERE recipe::jsonb->>'{meal[calories]}' = '900';
WHERE recipe::jsonb->>'meal[calories]' = '900';
WHERE recipe::jsonb#>'{meal, calories}' = '900';
WHERE recipe::jsonb->'{meal, calories}' = '900';
WHERE recipe::jsonb->'meal[calories]' = '900';
WHERE recipe::jsonb#>'{meal[calories]}' = '900';
WHERE recipe::jsonb->'{meal[calories]}' = '900';
WHERE recipe::jsonb#>>'{meal.calories}' = '900';
WHERE recipe::jsonb#>'{meal.calories}' = '900';
WHERE recipe::jsonb->>'{meal.calories}' = '900';
WHERE recipe::jsonb->'{meal.calories}' = '900';

Это приводит к ошибке (неправильный синтаксис):

SELECT * FROM Meal ...

WHERE recipe::json#>'{meal, calories}' = '900';
WHERE recipe::json->'{meal, calories}' = '900';
WHERE recipe::json#>>'meal[calories]' = '900';
WHERE recipe::json#>'meal[calories]' = '900';
WHERE recipe::json->'meal[calories]' = '900';
WHERE recipe::json#>'{meal[calories]}' = '900';
WHERE recipe::json->'{meal[calories]}' = '900';
WHERE recipe::json#>'{meal.calories}' = '900';
WHERE recipe::json->'{meal.calories}' = '900';

WHERE recipe::jsonb#>>'meal[calories]' = '900';
WHERE recipe::jsonb#>'meal[calories]' = '900';

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

Ответ 1

Чтобы выбрать еду:

select * from meal where recipe #>> '{meal,0,calories}' = '900';

Если вы хотите найти эти записи в массиве meal, вам нужно итерации массива, чтобы изучить каждый ключ. Там нет индекса подстановочных массивов или заполнителя имени объекта - вы не можете написать {meal,*,calories}. Пока нет; Функция json продолжает улучшаться.

Вот как бы я это сделал:

select meal.id, recipe_entry 
from meal,
lateral jsonb_array_elements(recipe -> 'meal') recipe_entry 
where CAST(recipe_entry ->> 'calories' AS integer) = 900;

Некоторые возможные улучшения в json-функциональности сделают это намного проще. Функция поиска пути, поддерживающая подстановочные знаки, которая могла бы вернуть набор, была бы очень полезна - вероятно, в качестве улучшений для json_extract_path. Возможно, в 9.5, если кто-то окажется нетерпеливым. Другая вещь, которая действительно помогла бы, была бы функцией преобразования или произнесением для json-скаляров, поэтому мы могли бы написать recipe_entry -> 'calories' = to_json(900) и получить Javascript-подобную семантику сравнения равенства, а не полагаться на вышеприведенный листинг.