PostgreSQL: Paginate JSONB data

I have used both MongoDB and PostgreSQL (using JSONB) and IMO, PostgreSQL wins 90% of the time.

This is because most data in real-life is inherently relational and PostgreSQL gives you the best of both worlds. It's a powerful relational database but also has the flexibility of JSONB when required (e.g. JSON can be perfect for unstructured data).

It's disadvantages are at humongous (cough) scale - MongoDB can win then e.g. when there are huge amounts of raw JSON data (or data which can be easily converted to JSON) with no/limited relations.

The power of PostgreSQL JSONB is best illustrated with an example: -

Lets create a table (t) as follows: -

create table t (
    id serial primary key, 
    data jsonb); 

... with some demo data ...

insert into t (id, data) 
       values (1, '[{"name": "A", "age": 20},
                    {"name": "B", "age": 21},
                    {"name": "C", "age": 22},
                    {"name": "D", "age": 23},
                    {"name": "E", "age": 24},
                    {"name": "F", "age": 25},
                    {"name": "G", "age": 26}]'),
              (2, '[{"name": "H", "age": 27},
                    {"name": "I", "age": 28},
                    {"name": "J", "age": 29},
                    {"name": "K", "age": 30},
                    {"name": "L", "age": 31}]'),
              (3, '[{"name": "M", "age": 32},
                    {"name": "N", "age": 33},
                    {"name": "O", "age": 34},
                    {"name": "P", "age": 35},
                    {"name": "Q", "age": 36}]');

1. Simple select

If we simply select all from t we get 3 rows with a JSONB array in the data column.

select * 
  from t;  

----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 id | data
----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | [{"age": 20, "name": "A"}, {"age": 21, "name": "B"}, {"age": 22, "name": "C"}, {"age": 23, "name": "D"}, {"age": 24, "name": "E"}, {"age": 25, "name": "F"}, {"age": 26, "name": "G"}]
  2 | [{"age": 27, "name": "H"}, {"age": 28, "name": "I"}, {"age": 29, "name": "J"}, {"age": 30, "name": "K"}, {"age": 31, "name": "L"}]
  3 | [{"age": 32, "name": "M"}, {"age": 33, "name": "N"}, {"age": 34, "name": "O"}, {"age": 35, "name": "P"}, {"age": 36, "name": "Q"}]
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2. Select + Unnest

We can then "un-nest" the JSONB array in the data column by using the jsonb_array_elements function - this will return 17 rows of id and data JSONB objects.

select id, 
       jsonb_array_elements(data) 
  from t;

----+--------------------------
 id | data
----+--------------------------
  1 | {"age": 20, "name": "A"}
  1 | {"age": 21, "name": "B"}
  1 | {"age": 22, "name": "C"}
  1 | {"age": 23, "name": "D"}
  1 | {"age": 24, "name": "E"}
  1 | {"age": 25, "name": "F"}
  1 | {"age": 26, "name": "G"}
  2 | {"age": 27, "name": "H"}
  2 | {"age": 28, "name": "I"}
  2 | {"age": 29, "name": "J"}
  2 | {"age": 30, "name": "K"}
  2 | {"age": 31, "name": "L"}
  3 | {"age": 32, "name": "M"}
  3 | {"age": 33, "name": "N"}
  3 | {"age": 34, "name": "O"}
  3 | {"age": 35, "name": "P"}
  3 | {"age": 36, "name": "Q"}
-------------------------------

3. Select + Unnest + Pagination

We can then paginate the previous "un-nested" query above: -

select id, 
       jsonb_array_elements(data) 
  from t 
 limit 5; -- return 1st 5 

----+---------------------------
 id | data
----+---------------------------
  1 | {"age": 20, "name": "A"}
  1 | {"age": 21, "name": "B"}
  1 | {"age": 22, "name": "C"}
  1 | {"age": 23, "name": "D"}
  1 | {"age": 24, "name": "E"}
--------------------------------

select id, 
       jsonb_array_elements(data) 
  from t 
 limit 5 offset 5; -- return next 5

----+---------------------------
 id | data
----+---------------------------
  1 | {"age": 25, "name": "F"}
  1 | {"age": 26, "name": "G"}
  2 | {"age": 27, "name": "H"}
  2 | {"age": 28, "name": "I"}
  2 | {"age": 29, "name": "J"}
--------------------------------

4. Select + Un-nest + Pagination + Re-nest

We can take this 1 step further and can group by id again and put the JSON back into an array using the jsonb_agg function: -

with t_unnested as (
    select id, 
           jsonb_array_elements(data) as data
      from t 
     limit 5 offset 5
)
select id, jsonb_agg (data) 
  from t_unnested
 group by id;

----+--------------------------------------------------------------------------------
 id | data
----+--------------------------------------------------------------------------------
  1 | [{"age": 25, "name": "F"}, {"age": 26, "name": "G"}]
  2 | [{"age": 27, "name": "H"}, {"age": 28, "name": "I"}, {"age": 29, "name": "J"}]
----+--------------------------------------------------------------------------------

5. Select + Un-nest + Pagination + Re-nest + Custom Object

We can take the previous query and re-construct a new object with new fields e.g. person_id and person_info. This will return a single column with a new custom JSONB object (again a row per id).

with t_unnested as (
    select id, 
           jsonb_array_elements(data) as data
      from t 
     limit 5 offset 5
),
t_person as (
    select jsonb_build_object (
               'person_id', id,
               'person_info', jsonb_agg (data)
           ) as person
      from t_unnested
     group by id
)
select person from t_person;

-----------------------------------------------------------------------------------------------------------------
 person
-----------------------------------------------------------------------------------------------------------------
 {"person_id": 1, "person_info": [{"age": 25, "name": "F"}, {"age": 26, "name": "G"}]}
 {"person_id": 2, "person_info": [{"age": 27, "name": "H"}, {"age": 28, "name": "I"}, {"age": 29, "name": "J"}]}
-----------------------------------------------------------------------------------------------------------------

6. Select + Un-nest + Pagination + Re-nest + Custom Object + Further Re-Nest

The previous query returned 2 rows, we can create a single row by once again using the jsonb_agg function i.e.

with t_unnested as (
    select id, 
           jsonb_array_elements(data) as data
      from t 
     limit 5 offset 5
),
t_person as (
    select jsonb_build_object (
               'person_id', id,
               'person_info', jsonb_agg (data)
           ) as person
      from t_unnested
     group by id
)
select jsonb_agg(person) from t_person;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 person
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"person_id": 1, "person_info": [{"age": 25, "name": "F"}, {"age": 26, "name": "G"}]}, {"person_id": 2, "person_info": [{"age": 27, "name": "H"}, {"age": 28, "name": "I"}, {"age": 29, "name": "J"}]}]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hopefully this shows the power of JSONB / PostgreSQL in both storing JSONB, un-nesting (and re-nesting) JSON arrays with pagination.