CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpsql
BaskarA
BaskarA

Reputation: 75

Querying based on JSON array sub element

Tried multiple answers from here and elsewhere and couldn't find the right answer yet.

create table mstore (
muuid uuid PRIMARY KEY,
msid text,
m_json JSONb[] not NULL
);

inserted first row:

insert into mstore (muuid, msid, m_json) values (
'3b691440-ee54-4d9d-a5b3-5f1863b78755'::uuid,
'<163178891004.4772968682254423915@XYZ-73SM>',
(array['{"m": 123, "mts": "2021-09-16T10:53:43.599012", "dstatus": "Dropped", "rcpt": "[email protected]"}']::jsonb[])
);

inserted second row:

insert into mstore (muuid, msid, m_json) values (
'3b691440-ee54-4d9d-a5b3-5f1863b78757'::uuid,
'<163178891004.4772968682254423915@XYZ-75SM>',
(array['{"m": 125, "mts": "2021-09-16T10:53:43.599022", "dstatus": "Dropped", "rcpt": "[email protected]"}']::jsonb[])
);

updated the first row:

update mstore
set m_json = m_json || '{"m": 124, "mts": "2021-09-16T10:53:43.599021", "dstatus": "Delivered", "rcpt": "[email protected]"}'::jsonb
where muuid = '3b691440-ee54-4d9d-a5b3-5f1863b78755';

Now table looks like:

               muuid               |                          msid                          |                                                                                                                 m_json                                                                                                                  
--------------------------------------+----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 3b691440-ee54-4d9d-a5b3-5f1863b78757 | <163178891004.4772968682254423915@XYZ-75SM> | {"{\"mid\": 125, \"rcpt\": \"[email protected]\", \"msg_ts\": \"2021-09-16T10:53:43.599022\", \"dstatus\": \"Dropped\"}"}
 3b691440-ee54-4d9d-a5b3-5f1863b78755 | <163178891004.4772968682254423915@XYZ-73SM> | {"{\"mid\": 123, \"rcpt\": \"[email protected]\", \"msg_ts\": \"2021-09-16T10:53:43.599012\", \"dstatus\": \"Dropped\"}","{\"mid\": 124, \"rcpt\": \"[email protected]\", \"msg_ts\": \"2021-09-16T10:53:43.599021\", \"dstatus\": \"Delivered\"}"}

Now, I need to query based on the status. I tried few but most relevant one was

select * from mstore,jsonb_array_elements(m_json) with ordinality arr(item_object, position)  where item_object->>'{"dstatus": "Delivered"}';

and

select * from mstore where m_json @> '[{"dstatus": "Delivered"}]';

Neither work, as they have syntax errors. How to run this query with dstatus values?

Upvotes: 1

Views: 67

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13029

Please note that mstore.m_json is a Postgres array of JSONB elements and not a JSONB array and therefore unnest must be used rather than jsonb_array_elements. Also have a look at ->> operator in the documentation.
The same applies to your second example. It would work if mstore.m_json is a JSONB array and not a Postgres array of JSONB elements.

select m.muuid, m.msid, l.item_object, l.pos
from mstore m
cross join lateral unnest(m.m_json) with ordinality l(item_object, pos)
where l.item_object ->> 'dstatus' = 'Delivered';

It would be better to use JSONB data type for column mstore.m_json rather than JSONB[] or - much better - normalize the data design.

Upvotes: 1

Related Questions