I have such a PostgreSQL table
id data(jsonb)
-- ------------------------------------
23 {"val": [30, 5, 1]}
41 {"val": [11, 4, 99]}
99 {"val": [2]}
Now I would like to get all sorted val and have the limitation for each query (for pagination). For example 1st query
val
-----
1
2
4
5
11
2nd query
val
-----
30
99
You need to unnest the array, sort the result then apply offset and limit:
select v.value::int
from the_table t
cross join jsonb_array_elements_text(t.data -> 'val') as v(value)
order by v.value::int
offset 0 --<< start of page
limit 5 --<< page size
;
Instead of limit 5 you can also use the standard compliant fetch first 5 rows only
You can use a cte to transform the JSON data and then apply the pagination logic in the main query:
with results(id, v) as (
select row_number() over (order by v.value::int), v.value::int from tbl t
cross join jsonb_array_elements(t.data -> 'val') v
)
select r.v from results r where 5*(<query_num> - 1) < r.id and r.id <= 5*(<query_num> - 1) + 5
