Rails + PostgreSQL JSONB (Part 3)
Tags: rails, postgres
Published: 2020-06-28 14:44:32

| Rails + PostgreSQL JSONB Series |
| --------------------------- |
| [Part 1: Migration and CRUD](https://dev.to/kputra/rails-postgresql-jsonb-part-1-4ibg) |
| [Part 2: store_accessor, Query, and Index](https://dev.to/kputra/rails-postgresql-jsonb-part-2-2fil) |
| **Part 3: Paginate JSONB Data** |
*If you continue to read this article, I assume that you know Ruby, OOP in Ruby, RoR, Active Record, and basic postgresql query*

This Part 3 is covering about how to Paginate JSONB Data. But first, I'll deep dive into "playing" with the JSONB data. If you are not interested into deep dive, **you can directly jump to [Chapter 3: Select + flatten + pagination](#chapter-3)**

The database schema in this article will be very different from [Part 1](https://dev.to/kputra/rails-postgresql-jsonb-part-1-4ibg) and [Part 2](https://dev.to/kputra/rails-postgresql-jsonb-part-2-2fil). No connection between the previous Parts and this part.

The schema we used in this article:
```ruby
class CreateUsers < ActiveRecord::Migration[5.2]
  def change
    create_table :users do |t|
      t.jsonb :payload, null: false, default: '{}'
    end
  add_index :users, :payload, using: :gin
  end
end
```
Then we seed our database with these data:
```ruby
irb(main):001:1* payload_1 = [
irb(main):002:1*   {"name": "A", "age": 20},
irb(main):003:1*   {"name": "B", "age": 21},
irb(main):004:1*   {"name": "C", "age": 22},
irb(main):005:1*   {"name": "D", "age": 23},
irb(main):006:1*   {"name": "E", "age": 24},
irb(main):007:1*   {"name": "F", "age": 25},
irb(main):008:1*   {"name": "G", "age": 26}
irb(main):009:0> ]
irb(main):010:0>
irb(main):011:1* payload_2 = [
irb(main):012:1*   {"name": "H", "age": 27},
irb(main):013:1*   {"name": "I", "age": 28},
irb(main):014:1*   {"name": "J", "age": 29},
irb(main):015:1*   {"name": "K", "age": 30},
irb(main):016:1*   {"name": "L", "age": 31}
irb(main):017:0> ]
irb(main):018:0>
irb(main):019:1* payload_3 = [
irb(main):020:1*   {"name": "H", "age": 27},
irb(main):021:1*   {"name": "I", "age": 28},
irb(main):022:1*   {"name": "J", "age": 29},
irb(main):023:1*   {"name": "K", "age": 30},
irb(main):024:1*   {"name": "L", "age": 31}
irb(main):025:0> ]
irb(main):026:0> User.create(payload: payload_1)
   (0.1ms)  BEGIN
  User Create (1.0ms)  INSERT INTO "users" ("payload", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"  [["payload", "[{\"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}]"], ["created_at", "2020-06-29 00:05:49.725982"], ["updated_at", "2020-06-29 00:05:49.725982"]]
   (0.3ms)  COMMIT
=> #<User id: 1, payload: [{"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}], created_at: "2020-06-29 00:05:49", updated_at: "2020-06-29 00:05:49">
irb(main):027:0> User.create(payload: payload_2)
   (0.1ms)  BEGIN
  User Create (0.3ms)  INSERT INTO "users" ("payload", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"  [["payload", "[{\"name\":\"H\",\"age\":27},{\"name\":\"I\",\"age\":28},{\"name\":\"J\",\"age\":29},{\"name\":\"K\",\"age\":30},{\"name\":\"L\",\"age\":31}]"], ["created_at", "2020-06-29 00:05:49.804221"], ["updated_at", "2020-06-29 00:05:49.804221"]]
   (0.5ms)  COMMIT
=> #<User id: 2, payload: [{"name"=>"H", "age"=>27}, {"name"=>"I", "age"=>28}, {"name"=>"J", "age"=>29}, {"name"=>"K", "age"=>30}, {"name"=>"L", "age"=>31}], created_at: "2020-06-29 00:05:49", updated_at: "2020-06-29 00:05:49">
irb(main):028:0> User.create(payload: payload_3)
   (0.1ms)  BEGIN
  User Create (0.3ms)  INSERT INTO "users" ("payload", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"  [["payload", "[{\"name\":\"H\",\"age\":27},{\"name\":\"I\",\"age\":28},{\"name\":\"J\",\"age\":29},{\"name\":\"K\",\"age\":30},{\"name\":\"L\",\"age\":31}]"], ["created_at", "2020-06-29 00:05:51.025203"], ["updated_at", "2020-06-29 00:05:51.025203"]]
   (0.8ms)  COMMIT
=> #<User id: 3, payload: [{"name"=>"H", "age"=>27}, {"name"=>"I", "age"=>28}, {"name"=>"J", "age"=>29}, {"name"=>"K", "age"=>30}, {"name"=>"L", "age"=>31}], created_at: "2020-06-29 00:05:51", updated_at: "2020-06-29 00:05:51">
```

So, we have 3 row of User.

Let's start our journey!

**Table of Contents:**
[1. Simple select](#chapter-1)
[2. Select + flatten](#chapter-2)
[3. Select + flatten + pagination](#chapter-3)
[4. Select + flatten + pagination + nested](#chapter-4)
[5. Select + flatten + pagination + nested + custom object](#chapter-5)
[6. Select + flatten + pagination + nested + custom object + nested](#chapter-6)

<a name="chapter-1"></a>
##1. Simple select
If we simply select all from `User` we get 3 rows with a JSONB array in the `payload` column.

```sql
SELECT * 
FROM users;  

----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 id | payload
----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  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"}]
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
```

In Rails (using Active Record), this method equal to: 
```ruby
irb(main):029:0> User.all
  User Load (0.3ms)  SELECT "users".* FROM "users" LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<User id: 1, payload: [{"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"}], created_at: "2020-06-29 00:05:49", updated_at: "2020-06-29 00:05:49">, #<User id: 2, payload: [{"age"=>27, "name"=>"H"}, {"age"=>28, "name"=>"I"}, {"age"=>29, "name"=>"J"}, {"age"=>30, "name"=>"K"}, {"age"=>31, "name"=>"L"}], created_at: "2020-06-29 00:05:49", updated_at: "2020-06-29 00:05:49">, #<User id: 3, payload: [{"age"=>27, "name"=>"H"}, {"age"=>28, "name"=>"I"}, {"age"=>29, "name"=>"J"}, {"age"=>30, "name"=>"K"}, {"age"=>31, "name"=>"L"}], created_at: "2020-06-29 00:05:51", updated_at: "2020-06-29 00:05:51">]>
irb(main):050:0>
```
<a name="chapter-2"></a>
##2. Select + flatten
We can then flatten 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.
```sql
SELECT id, 
       jsonb_array_elements(payload) 
FROM users;

----+--------------------------
 id | payload
----+--------------------------
  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"}
-------------------------------
```
In Rails (using Active Record), this method equal to:
```ruby
irb(main):030:0> User.select("id", "jsonb_array_elements(payload) AS payload").all
  User Load (0.4ms)  SELECT "users"."id", jsonb_array_elements(payload) AS payload FROM "users" LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<User id: 1, payload: {"age"=>20, "name"=>"A"}>, #<User id: 1, payload: {"age"=>21, "name"=>"B"}>, #<User id: 1, payload: {"age"=>22, "name"=>"C"}>, #<User id: 1, payload: {"age"=>23, "name"=>"D"}>, #<User id: 1, payload: {"age"=>24, "name"=>"E"}>, #<User id: 1, payload: {"age"=>25, "name"=>"F"}>, #<User id: 1, payload: {"age"=>26, "name"=>"G"}>, #<User id: 2, payload: {"age"=>27, "name"=>"H"}>, #<User id: 2, payload: {"age"=>28, "name"=>"I"}>, #<User id: 2, payload: {"age"=>29, "name"=>"J"}>, ...]>
```
<a name="chapter-3"></a>
##3. Select + flatten + pagination
We can then paginate the previous flatten query above.
```sql
SELECT id, 
       jsonb_array_elements(payload) 
FROM users 
LIMIT 5;

----+---------------------------
 id | payload
----+---------------------------
  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(payload) 
FROM users 
LIMIT 5 OFFSET 5;

----+---------------------------
 id | payload
----+---------------------------
  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"}
--------------------------------
```
In Rails (using Active Record), this method equal to: 
```ruby
irb(main):031:0> User.select("id", "jsonb_array_elements(payload) AS payload").all.limit(5).offset(5)
  User Load (0.3ms)  SELECT "users"."id", jsonb_array_elements(payload) AS payload FROM "users" LIMIT $1 OFFSET $2  [["LIMIT", 5], ["OFFSET", 5]]
=> #<ActiveRecord::Relation [#<User id: 1, payload: {"age"=>25, "name"=>"F"}>, #<User id: 1, payload: {"age"=>26, "name"=>"G"}>, #<User id: 2, payload: {"age"=>27, "name"=>"H"}>, #<User id: 2, payload: {"age"=>28, "name"=>"I"}>, #<User id: 2, payload: {"age"=>29, "name"=>"J"}>]>
```
The controller and routes might look like this:
```ruby
# app/controllers/users_controller.rb
class UsersController < ApplicationController
  def index
    page = params[:page].to_i
    per_page = params[:per_page].to_i
    offset = (page - 1) * per_page
    payload = User.select("id", "jsonb_array_elements(payload) AS payload").all.limit(per_page).offset(offset)
    render json: { object: payload }, status: 200
  end
end

# config/routes.rb
Rails.application.routes.draw do
  resources :users
end
```
Let's fire up the server with `rails s`, then go to `localhost:3000/users?page=1&per_page=5` in the browser/postman (GET method):
```json
{
    "object": [
        {
            "id": 1,
            "payload": {
                "age": 25,
                "name": "F"
            }
        },
        {
            "id": 1,
            "payload": {
                "age": 26,
                "name": "G"
            }
        },
        {
            "id": 2,
            "payload": {
                "age": 27,
                "name": "H"
            }
        },
        {
            "id": 2,
            "payload": {
                "age": 28,
                "name": "I"
            }
        },
        {
            "id": 2,
            "payload": {
                "age": 29,
                "name": "J"
            }
        }
    ]
}
```
If you want to only record from one User, say User with id 1, the controller might look like this:
```ruby
# app/controllers/users_controller.rb
class UsersController < ApplicationController
  def index
    payload = JSON.parse "[#{ActiveRecord::Base.connection.exec_query(sql).rows.join(',')}]"
    render json: { object: payload }, status: 200
  end

  private

  def sql
    "
    SELECT jsonb_array_elements(payload)
    FROM users
    WHERE id = #{params[:id]}
    LIMIT #{params[:per_page]} OFFSET #{(params[:page].to_i - 1) * params[:per_page].to_i}
    "
  end
end
```
Let's go to `localhost:3000/users?page=1&per_page=5&id=1` again, and the result will be:
```json
{
    "object": [
        {
            "age": 20,
            "name": "A"
        },
        {
            "age": 21,
            "name": "B"
        },
        {
            "age": 22,
            "name": "C"
        },
        {
            "age": 23,
            "name": "D"
        },
        {
            "age": 24,
            "name": "E"
        }
    ]
}
```
<a name="chapter-4"></a>
##4. Select + flatten + pagination + nested
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:
```sql
WITH users_unnested as (
    SELECT id, 
           jsonb_array_elements(payload) as payload
    FROM users 
    LIMIT 5 OFFSET 5
)
SELECT id, jsonb_agg (payload) 
FROM users_unnested
GROUP BY id;

----+--------------------------------------------------------------------------------
 id | payload
----+--------------------------------------------------------------------------------
  1 | [{"age": 25, "name": "F"}, {"age": 26, "name": "G"}]
  2 | [{"age": 27, "name": "H"}, {"age": 28, "name": "I"}, {"age": 29, "name": "J"}]
----+--------------------------------------------------------------------------------
```
<a name="chapter-5"></a>
##5. Select + flatten + pagination + nested + custom object
We can take the previous query and re-construct a new object with new fields e.g. user_id and user_info. This will return a single column with a new custom JSONB object (again a row per id).
```sql
WITH users_unnested as (
    SELECT id, 
           jsonb_array_elements(payload) as payload
    FROM users
    LIMIT 5 OFFSET 5
),
users_info as (
    SELECT jsonb_build_object (
               'user_id', id,
               'user_info', jsonb_agg (payload)
           ) as user
     FROM users_unnested
     GROUP BY id
)
SELECT user from users_info;

-----------------------------------------------------------------------------------------------------------------
 user
-----------------------------------------------------------------------------------------------------------------
 {"user_id": 1, "user_info": [{"age": 25, "name": "F"}, {"age": 26, "name": "G"}]}
 {"user_id": 2, "user_info": [{"age": 27, "name": "H"}, {"age": 28, "name": "I"}, {"age": 29, "name": "J"}]}
-----------------------------------------------------------------------------------------------------------------
```
<a name="chapter-6"></a>
##6. Select + flatten + pagination + nested + custom object + nested
The previous query returned 2 rows, we can create a single row by once again using the `jsonb_agg` function i.e.
```sql
WITH users_unnested as (
    SELECT id, 
           jsonb_array_elements(payload) as payload
    FROM users
    LIMIT 5 OFFSET 5
),
users_info as (
    SELECT jsonb_build_object (
               'user_id', id,
               'user_info', jsonb_agg (payload)
           ) as user
     FROM users_unnested
     GROUP BY id
)
SELECT jsonb_agg(user) from users_info;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 user
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"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"}]}]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
```
source: [here](https://stackoverflow.com/questions/55092756/postgresql-paginate-jsonb-data)