Clash Royale CLAN TAG#URR8PPP

Query for condition in array of JSON objects in PostgreSQL

Lets assume we have a PostgreSQL db with a table with rows of the following kind:

id | doc
---+-----------------
1 | JSON Object
2 | JSON Object
3 | JSON Object
...


The JSON has the following structure:


'header' :
'info' : 'foo',
'data' :
['a' : 1, 'b' : 123,
'a' : 2, 'b' : 234,
'a' : 1, 'b' : 543,
...
'a' : 1, 'b' : 123,
'a' : 4, 'b' : 452]



with arbitrary values for 'a' and 'b' in 'data' in all rows of the table.

First question: how do I query for rows in the table where the following condition holds:

There exists a dictionary in the list/array with the key 'data', where a==i and b>j.

For example for i=1 and j=400 the condition would be fulfilled for the example above and the respective column would be returned.

Second question:
In my problem I have to deal with time series data in Json. Every measurement is represented by one Json and therefore one row in the table. I want to identify measurements where certain events occurred. For the case that the above structure is unsuitable in terms of easy querying: How could such a time series look like to be more easily queryable?

Thanks a lot!

1 Answer
1

I believe a query like this should answer your first question:

select distinct id, doc
from (
select id, doc, jsonb_array_elements(doc->'data') as elem
from docs
) as docelem
where (elem->>'a')::int = 4 and (elem->>'b')::int > 400


db<>fiddle here

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.