Order and limit data based on JOSNB array-CodePudding

I have such a PostgreSQL table

id   data(josnb)
--   ------------------------------------
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

CodePudding user response：

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

CodePudding user response：

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