| 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
Top comments (0)