问题 如何在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';

如果您有任何建议，我将非常感谢您的聆听。

选择餐：

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的等式比较语义，而不是依赖于上面的强制转换。

我可能已经弄清楚了。根据OP中的表格，以下2个查询正确返回记录：（1）SELECT * FROM Meal WHERE recipe @>'{“meal”：[{“calories”：900}]}':: jsonb; （2）SELECT * FROM Meal WHERE recipe @>'{“meal”：[{“calories”：900}]}':: jsonb AND recipe @>'{“meal”：[{“fruit”：[{“ ID“：4}]}]}':: jsonb;并且以下查询将不返回记录（这是正确的，因为meal.fruit.id = 40不存在）:( 3）SELECT * FROM Meal WHERE recipe @>'{“meal”：[{“calories”：900 }'}':: jsonb AND recipe @>'{“meal”：[{“fruit”：[{“id”：40}]}]}':: jsonb;希望这可以帮助！ - bjones1831

在不到72小时的测试版中询问有关新功能的问题不会在SO上产生许多明智的答案，如果有的话。在这个上下文中的问题更好地在postgresql邮件列表之一上询问 - 想到了黑客。 - Patrick

我将此标记为答案，因为这有效...并且它还帮助我在比较中使用jsonb值（例如，将“= 900”到“> 900”的craig查询调整不会返回记录。这是正确的。 ..感谢您的帮助！我一直在努力找出今天大部分时间的比较。 - bjones1831

在PostgreSQL 9.5上，您可以使用@>运算符，如下所述： stackoverflow.com/questions/28486192/... - Andrés Torres Marroquín

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

答案:

热门问题

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

答案:

热门问题

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