Postgresql 查询嵌套 JSONB 字段中的对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42130740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql query for objects in nested JSONB field
提问by ifdog
I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like:
我使用的是 PostgreSQL 9.6，我有一个名为“ItemDbModel”的表，其中有两列，如下所示：
No integer,
Content jsonb
Say I put many records like:
假设我放了很多记录，例如：
"No": 2, {"obj":"x","Item": {"Name": "BigDog", "Model": "NamedHusky", "Spec":"red dog"}}
"No": 4, {"obj":"x","Item": {"Name": "MidDog", "Model": "NamedPeppy", "Spec":"no hair"}}
"No": 5, {"obj":"x","Item": {"Name": "BigCat", "Model": "TomCat", "Spec":"blue color"}}
How can I query the table for:
如何查询表：
- Records where "Content.Item.Name" contains "Dog" And"Content.Item.Spec" contains "red".
- Records where "Content.Item.Name" contains "Dog" OR"Content.Item.Spec" contains "red".
- Records where Anyjson fields in "Content.Item" contains "dog".
- “Content.Item.Name”包含“Dog”并且“Content.Item.Spec”包含“red”的记录。
- “Content.Item.Name”包含“Dog”或“Content.Item.Spec”包含“red”的记录。
- 记录“Content.Item”中的任何json 字段包含“dog”。
And order by "Content.Item.Name.length"?
并按“Content.Item.Name.length”排序？
Thank you!
谢谢！
回答by klin
You should become familiar with JSON Functions and Operators.
您应该熟悉JSON 函数和运算符。
-- #1
select *
from example
where content->'Item'->>'Name' ilike '%dog%'
and content->'Item'->>'Spec' ilike '%red%'
-- #2
select *
from example
where content->'Item'->>'Name' ilike '%dog%'
or content->'Item'->>'Spec' ilike '%red%'
-- #3
select distinct on(no) t.*
from example t,
lateral jsonb_each_text(content->'Item')
where value ilike '%dog%';
-- and
select *
from example t
order by length(content->'Item'->>'Name');
Postgres 12introduces new features implementing the SQL/JSON Path Language. Alternative queries using the jsonpathmay look like this:
Postgres 12引入了实现 SQL/JSON 路径语言的新功能。使用 的替代查询jsonpath可能如下所示：
-- #1
select *
from example
where jsonb_path_exists(
content,
'$ ? ($.Item.Name like_regex "dog" flag "i" && $.Item.Spec like_regex "red" flag "i")');
-- #2
select *
from example
where jsonb_path_exists(
content,
'$ ? ($.Item.Name like_regex "dog" flag "i" || $.Item.Spec like_regex "red" flag "i")');
-- #3
select *
from example
where jsonb_path_exists(
content,
'$.Item.* ? (@ like_regex "dog" flag "i")');
The first two queries are basically similar to the previous ones and the ->syntax may seem simpler and more pleasant than jsonpathone. Particular attention should be paid to the third query, which uses a wildcard so it eliminates the need for using the expensive function jsonb_each_text ()and should be significantly faster.
前两个查询与前两个查询基本相似，->语法可能看起来比jsonpath一个更简单、更愉快。应该特别注意第三个查询，它使用通配符，因此不需要使用昂贵的函数，jsonb_each_text ()并且应该明显更快。
Read in the documentation:
阅读文档：
