PostgreSQL查询嵌套JSONB字段中的对象 [英] Postgresql query for objects in nested JSONB field

问题描述

我正在使用PostgreSQL 9.6，并且我有一个名为"ItemDbModel"的表，其中有两列，如下所示:

I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like:

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

如何查询表中的内容

1. "Content.Item.Name"包含"Dog"的记录，而"Content.Item.Spec"包含"red"的记录.
2. "Content.Item.Name"包含"Dog"的记录 OR "Content.Item.Spec"包含"red"的记录.
3. 记录"Content.Item"中的任何 json字段包含"dog"的记录.

1. Records where "Content.Item.Name" contains "Dog" And "Content.Item.Spec" contains "red".
2. Records where "Content.Item.Name" contains "Dog" OR "Content.Item.Spec" contains "red".
3. Records where Any json fields in "Content.Item" contains "dog".

然后按"Content.Item.Name.length"排序吗?

And order by "Content.Item.Name.length"?

谢谢！

推荐答案

您应该熟悉 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的替代查询可能看起来像这样:

Postgres 12 introduces new features implementing the SQL/JSON Path Language. Alternative queries using the jsonpath may look like this:

-- #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 ()的需要，并且应显着更快.

The first two queries are basically similar to the previous ones and the -> syntax may seem simpler and more pleasant than jsonpath one. 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.

阅读文档:

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

• The SQL/JSON Path Language
• jsonpath Type

这篇关于PostgreSQL查询嵌套JSONB字段中的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！