Postgres递归json限制

Question

引用Postgresql-jonb遍历

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;

在末尾添加limit确实限制了返回，但似乎在内部检查了每条记录。

有可能限制在工会内部吗？

这种查询在大型数据集上非常困难。然而，我确实看到我可以在平面选择中限制时间戳范围。

Answer 1

如果要限制行数，则应在初始查询中添加order by和limit，例如：

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'])

或额外的CCD_ 3子句(在初始查询中(来根据条件过滤行。