Querying based on JSON array sub element

Question

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?

I think you cant query this way with SQL, but it is possible to query data in an array using NoSQL(firebase firestore). — S.Sachith
– S.Sachith, Commented Oct 4, 2021 at 3:04
It works in this blog levelup.gitconnected.com/… But not for my problem. But for sure, the blog is able to query using JSON Array's sub element — BaskarA
– BaskarA, Commented Oct 4, 2021 at 3:24

Stefanov.sm · Accepted Answer · 2021-10-04 07:36:34Z

1

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.

edited Oct 4, 2021 at 7:36

answered Oct 4, 2021 at 6:50

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying based on JSON array sub element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related