Rails + PostgreSQL JSONB (Part 3)

Rails + PostgreSQL JSONB Series
Part 1: Migration and CRUD
Part 2: store_accessor, Query, and Index
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

The database schema in this article will be very different from Part 1 and Part 2. No connection between the previous Parts and this part.

The schema we used in this article:

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:

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
2. Select + flatten
3. Select + flatten + pagination
4. Select + flatten + pagination + nested
5. Select + flatten + pagination + nested + custom object
6. Select + flatten + pagination + nested + custom object + nested

1. Simple select

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

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:

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>

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.

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:

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"}>, ...]>

3. Select + flatten + pagination

We can then paginate the previous flatten query above.

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:

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:

# 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):

{
    "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:

# 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:

{
    "object": [
        {
            "age": 20,
            "name": "A"
        },
        {
            "age": 21,
            "name": "B"
        },
        {
            "age": 22,
            "name": "C"
        },
        {
            "age": 23,
            "name": "D"
        },
        {
            "age": 24,
            "name": "E"
        }
    ]
}

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:

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

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).

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"}]}
----------------------------------------------------------------------------------------------------------------------

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.

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