如何在Postgresql 9.4中访问嵌套的JSON？

我正在尝试访问Postgresql 9.4中的嵌套jsonb字段.但是我很难根据嵌套的jsonb值检索记录.有人有这个成功吗？

首先,我创建了表:

CREATE TABLE Meal (
    id INT
  , recipe JSONB
);

其次,我将以下记录插入dbo.Meal :(我通过jsonlint.com运行json,它返回有效)

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

如果您有任何建议,我将非常感谢听到他们.