Postgresql query for objects in nested JSONB field

Question

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

How can I query the table for:

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

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

Thank you!

Answer 1

You should become familiar with JSON Functions and Operators.

-- #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 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")');

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.

Read in the documentation:

• The SQL/JSON Path Language
• jsonpath Type