Postgresql查询嵌套JSONB字段中的对象

Question

我正在使用 PostgreSQL 9.6，我有一个名为 "ItemDbModel" 的表，其中有两列，看起来像这样：

No integer,
Content jsonb

假设我放了很多记录，例如：

 "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"}}

如何查询以下条件的数据:

1. "Content.Item.Name" 包含 "Dog" 并且 "Content.Item.Spec" 包含 "red"。
2. "Content.Item.Name" 包含 "Dog" 或者 "Content.Item.Spec" 包含 "red"。
3. "Content.Item" 中任意一个 JSON 字段包含 "dog"。

并按照 "Content.Item.Name.length" 进行排序?

谢谢！

- ifdog

Answer 1

你应该熟悉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 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")');

前两个查询与先前的查询基本相同，-> 语法可能比 jsonpath 更简单和更易懂。需要特别注意第三个查询，它使用通配符，因此可以消除使用昂贵函数 jsonb_each_text() 的需要，并且应该会更快。

阅读文档：

• SQL/JSON 路径语言
• jsonpath 类型

- klin

---

如何选择使用 '->' 和 '->>' 之间的区别？ - Cheok Yan Cheng

-> 操作符返回一个 JSON 对象，而 ->> 返回文本。 - klin

说实话，我对Postgres感到失望。这些运算符令人困惑且不必要。 - Damien Roche

@jstuartmilne - i代表不区分大小写的匹配，请参阅9.15.2.2. 正则表达式了解详细信息。 - klin

无懈可击的回答 @klin - Gaurav