CODEWITHSUNDEEP
postgresql
Hommer Smith
Hommer Smith

Reputation: 27852

How to query deep jsonb in Postgres?

I have a jsonb column (called info) in Postgres which structure looks like this:

{ name: 'john', house_id: null, extra_attrs: [{ attr_id: 4, attr_value: 'a value' }, { attr_id: 5, attr_value: 'another value' }] }

It can have N extra_attrs but we know that each of them will have just two keys: the attr_id and the attr_value.

Now, what is the best way to query for info that has extra_attrs with a specific attr_id and attr_value. I have done it like this, and it works:

Given the following data structure to query for:

[{ attr_id: 4, values: ['a value', 'something else'] }, { attr_id: 5, values: ['another value'] }]

The following query works:

select * from people
where (info @> '{"extra_attrs": [{ "attr_id": 4, "attr_value": "a value" }]} OR info @> '{"extra_attrs": [{ "attr_id": 4, "attr_value": "something else" }]) AND info @> '{"extra_attrs": [{ "attr_id": 5, "attr_value": "another value" }]}

I am wondering if there is a better way to do so or this is fine.

Upvotes: 2

Views: 520

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28233

One alternate method would involve json functions and transforming data to apply the filter on:

SELECT people.info
FROM people,
LATERAL (SELECT DISTINCT True is_valid
         FROM jsonb_array_elements(info->'extra_attrs') y
         WHERE (y->>'attr_id', y->>'attr_value') IN (
             ('4', 'a value'), 
             ('4', 'something else'),
             ('5','another value')
         )
) y
WHERE is_valid

I believe this method more convenient for dynamic filters since the id/value pairs are added in only 1 place.

A similar (and perhaps slightly faster) method would use WHERE EXISTS and compare json documents like below.

SELECT people.info
FROM people
WHERE EXISTS (SELECT TRUE 
       FROM jsonb_array_elements(info->'extra_attrs') attrs
       WHERE attrs @> ANY(ARRAY[
           JSONB '{ "attr_id": 4, "attr_value": "a value" }',
           JSONB '{ "attr_id": 4, "attr_value": "something else" }',
           JSONB '{ "attr_id": 5, "attr_value": "another value" }'
              ]
       )
)

Upvotes: 1

Related Questions