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