Query for condition in array of JSON objects in PostgreSQL

Question

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!

Timshel · Accepted Answer · 2018-08-10 00:59:34Z

3

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

answered Aug 10, 2018 at 0:59

Timshel

1,78312 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Query for condition in array of JSON objects in PostgreSQL

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