Postgres recursive json limiting

Question

with recursive flat (id, timestamp, path, value) 
as (select id, timestamp, key, value 
    from trending_snapshot, jsonb_each(snapshot)
union select f.id, f.timestamp, j.key, j.value 
  from flat f, jsonb_each(f.value) j 
  where jsonb_typeof(f.value) = 'object' )
select timestamp, path, (value->>'value')::float AS value 
from flat 
where path like any(array['%Run01TubingPressure'])
limit 12;

Adding limit at the end does limit the return but it seems as if inside the with every record is checked.

Is it possible to limit inside a with union?

This query suffers terribly on large data sets. I do see however that I can limit the timestamp range in the flat select.

klin · Accepted Answer · 2018-07-20 18:56:05Z

2

If you are going to limit the number of rows you should add order by and limit in the initial query, e.g.:

with recursive flat (id, timestamp, path, value) as (
    (select id, timestamp, key, value 
    from trending_snapshot, 
    jsonb_each(snapshot)
    order by id
    limit 12)
union all
    select f.id, f.timestamp, j.key, j.value 
    from flat f, 
    jsonb_each(f.value) j 
    where jsonb_typeof(f.value) = 'object' )
select timestamp, path, (value->>'value')::float AS value 
from flat 
where path like any(array['%Run01TubingPressure'])

or extra where clause (in the initial query) to filter rows according to a condition.

answered Jul 20, 2018 at 18:56

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Michaela Ervin Over a year ago

Interesting. Ultimately we are trying to increase performance. So far it is faster to just do a "select * from trending_snapshot limit X;" and then parse the json in a python script.

klin Over a year ago

This may be a proper direction as recursion is not a strong point of RDBMS (in the context of performance).

Michaela Ervin Over a year ago

Oh ok. Thank you!

Collectives™ on Stack Overflow

Postgres recursive json limiting

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related