중첩된 JSONB 필드의 개체에 대한 Postgresql 쿼리

중첩된 JSONB 필드의 개체에 대한 Postgresql 쿼리

Postgre를 사용하고 있습니다.SQL 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. "내용"을 기록합니다.Item.Name에는 "Dog"와 "Content"가 포함되어 있습니다.Item.Spec에 빨간색이 포함되어 있습니다.
2. "내용"을 기록합니다.Item.Name에는 "Dog" 또는 "Content"가 포함되어 있습니다.Item.Spec에 빨간색이 포함되어 있습니다.
3. "Content"에서 임의의 json 필드의 위치를 기록합니다.Item"에는 "dog"가 포함되어 있습니다.

그리고 "내용"으로 주문합니다.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 Path Language를 구현하는 새로운 기능이 도입되었습니다.를 사용한 대체 쿼리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 유형

언급URL : https://stackoverflow.com/questions/42130740/postgresql-query-for-objects-in-nested-jsonb-field