DEV Community

loading...

Rails + PostgreSQL JSONB (Part 3)

kputra profile image K Putra ・8 min read
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
Enter fullscreen mode Exit fullscreen mode

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">
Enter fullscreen mode Exit fullscreen mode

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"}]
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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"}
-------------------------------
Enter fullscreen mode Exit fullscreen mode

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"}>, ...]>
Enter fullscreen mode Exit fullscreen mode

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"}
--------------------------------
Enter fullscreen mode Exit fullscreen mode

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"}>]>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

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"}]
----+--------------------------------------------------------------------------------
Enter fullscreen mode Exit fullscreen mode

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"}]}
-----------------------------------------------------------------------------------------------------------------
Enter fullscreen mode Exit fullscreen mode

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"}]}]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Enter fullscreen mode Exit fullscreen mode

source: here

Discussion (0)

pic
Editor guide