Sara Alhaddadi

Posted on Aug 5, 2022

Insert and query jsonb in rails and postgres

#rails #jsonb #postgres #sql

After 2 posts about jsonb in Postgres which contain information about it... Now is the time to make our hands dirty with jsonb...🔥

Add column

To work with jsonb let’s first create a table and add jsonb column

as database command like

CREATE TABLE cards (
  id integer NOT NULL,
 board_id integer 
  data jsonb
);

or in rails migration

create_table :books do |t|
   t.string :title
   t.jsonb :payload, null: false
, default: {}
   t.timestamps
end

or we can just add jsonb column to the existing table in rails migration

add_column :users, :data_jsonb, :jsonb

Insert data

To insert JSON data into the database by row SQL we pass the whole JSON value as a string with single quotes.

INSERT INTO books (title, payload,created_at,updated_at) VALUES ('book12','{"price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}],
"publisher": "publisher1", "published_date": "2017-15-07"}', '2022-15-05 19:55:21.364624', '2022-05-05 19:55:21.364624');

In rails work with it as hash

book_payload = {
  publisher: 'Currency',
  published_date: '2017-04-07',
  authors: [{id: 1,name: 'Sean Ellis'},{id: 2,name: 'Morgan Brown'}]
}
Book.create(title: "\"book1\", payload: book_payload)"

Show data

It is very simple to show the data in rails.
Note that any column defined as JSONB will be represented as a hash in rails, with all keys returned as a string.

> Book.first.payload[:publisher]
 => nil 
> Book.first.payload['publisher']
 => "Currency
> Book.last.payload['authors'][0]['name']
 => "Sean Ellis"

json and jsonb Operators

this table from PostgreSQL shows the operators that are available for use with the two JSON data types

Operator	Right Operand Type	Description	Example	Example Result
->	int	Get JSON array element (indexed from zero, negative integers count from the end)	'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2	{"c":"baz"}
->	text	Get JSON object field by key	'{"a": {"b":"foo"}}'::json->'a'	{"b":"foo"}
->>	int	Get JSON array element as text	'[1,2,3]'::json->>2	3
->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'	2
#>	text[]	Get JSON object at specified path	'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'	{"c": "foo"}
#>>	text[]	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'	3

To query JSON data, build your SELECT statement, as

-> returns value as exact JSONB data type
->> returns value as text.

Those can take the name of key for query in jsonb or the index if the query in jsonb array as key should be text with single quotation and index should be an integer

ok but now When to use -> and ->> ?
use -> when you want to return value as it is with its type and ->> when want value as string ( postures text type) for example:
use the -> operator to keep returning objects as chain until we reach the final attribute that we want where it’s ok to use ->> to return as text

SELECT pg_typeof(payload -> 'authors' ->> 1) AS author FROM "books" Where id = 20;
# author 
# --------
# text
# (1 row)

look here to get value of index 1 from authors we want to get authors as array to be able to look for index inside it but for the value of index 1 we need to return it as text as it is OK because we did not care about it is type.

do not use quotation marks (') with the name of the column. Whereas the attributes of the JSON always use quotation marks (‘).

the same thing for #> and #>> but here they will take an array of text for the path to get the value of the key or element in jsonb array

examples :

In rails

=> Book.select("payload -> 'authors' -> 1 AS author").map(&:author)
=> [nil, {"id"=>2, "name"=>"Morgan Brown"}, {"id"=>3, "name"=>"Morgan Browns"}]
>Book.select("payload -> 'authors' ->> 1 AS author").map(&:author)
=> [nil, "{\"id\": 2, \"name\": \"Morgan Brown\"}", "{\"id\": 3, \"name\": \"Morgan Browns\"}"]?
>Book.select("payload ->> 'publisher' AS publisher").map(&:publisher)
=> [nil, "Currency", "Currency2"]
# it is like Book.select("payload -> 'authors' -> 1 AS author").map(&:author)
Book.select("payload #> '{authors, 1}' AS author").map(&:author)
=> [nil, {"id"=>2, "name"=>"Morgan Brown"}, {"id"=>3, "name"=>"Morgan Browns"}] 
Book.select("payload #>> '{authors, 1,name}' AS author_name").map(&:author_name)
=> [nil, "Morgan Brown", "Morgan Browns"]

to check the return type of -> and ->> in Postgres

SELECT jsonb_typeof(payload -> 'authors' -> 1) AS author FROM "books" Where id = 20;
# author 
# --------
# object
# (1 row)

SELECT pg_typeof(payload -> 'authors' ->> 1) AS author FROM "books" Where id = 20;
# author 
# --------
# text
# (1 row)

or use them with equality to filter data ...

Note : we did not have key 0 as indexes should be an integer

Book.where("payload -> 'authors' -> '0' ->> 'name' = :name", name: 'author1')
# => []
Book.where("payload -> 'authors' -> 0 ->> 'name' = :name", name: 'author1')
# Book Load (0.6ms) SELECT "books".* FROM "books" WHERE (payload -> 'authors' -> 0 ->> 'name' = 'author1')
# => 
# [#<Book:0x000055b6f13b8528
# id: 22,
# title: "book3",
# payload:
# {"price"=>170, "authors"=>[{"id"=>1, "name"=>"author1"}, {"id"=>3, "name"=>"author3"}], "publisher"=>"publisher2", "published_date"=>"2018-04-07"},
# created_at: Sat, 07 May 2022 18:44:33.087149000 UTC +00:00,
# updated_at: Sat, 07 May 2022 18:44:33.087149000 UTC +00:00>,
# #<Book:0x000055b6f13b8438
# id: 20,
# title: "book1",
# payload:
# {"tags"=>["tag3"],
# "price"=>100,
# "authors"=>[{"id"=>1, "name"=>"author1"}, {"id"=>2, "name"=>"author2"}],
# "publisher"=>"new publisher",
# "published_date"=>"2017-04-07"},
# created_at: Sat, 07 May 2022 18:44:33.055553000 UTC +00:00,
# updated_at: Sat, 07 May 2022 18:44:33.055553000 UTC +00:00>]

same as

Book.where("payload #>> '{authors,0,name}' = :name", name: 'author1')
Book.where("payload -> 'authors' -> 0 @> :val", val: { name: 'author1' }.to_json)

or we can say returns book that has an empty payload

SELECT "books".* FROM "books" WHERE (payload = '{}');
# id | title | payload | created_at | updated_at 
# ----+-------+---------+----------------------------+----------------------------
# 35 | book5 | {} | 2022-06-11 18:30:03.332863 | 2022-06-11 18:30:03.332863
# (1 row)

Book.where("payload = :val", val: {}.to_json)
# Book Load (0.4ms) SELECT "books".* FROM "books" WHERE (payload = '{}')
# => 
# [#<Book:0x000055b6eec4f428
# id: 35,
# title: "book5",
# payload: {},
# created_at: Sat, 11 Jun 2022 18:30:03.332863000 UTC +00:00,
# updated_at: Sat, 11 Jun 2022 18:30:03.332863000 UTC +00:00>] 
remember to use  to_json to keep the same hash converted to the correct JSON data type
Book.where("payload = :val", val: {})
# Book Load (0.4ms) SELECT "books".* FROM "books" WHERE (payload = NULL)
# => []

look to query it is WHERE (payload = NULL) as here we need empty {} not Null

Additional jsonb only Operators

Some further operators also exist only for jsonb, as shown in this table from PostgreSQL
Many of these operators can be indexed by jsonb operator classes:

Operator	Right Operand Type	Description	Example
@>	jsonb	Does the left JSON value contain within it the right value?	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@	jsonb	Is the left JSON value contained within the right value?	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?	text	Does the key/element string exist within the JSON value?	'{"a":1, "b":2}'::jsonb ? 'b'
?		text[]	Do any of these key/element strings exist
?&	text[]	Do all of these key/element strings exist?	'["a", "b"]'::jsonb ?& array['a', 'b']

examples :

@> and <@ :

SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb ;
# ?column? 
# ----------
# t
# (1 row)

SELECT '{"a":1, "b":2}'::jsonb @> '{"b":3}'::jsonb ;
# ?column? 
# ----------
# f
# (1 row)

is the whole value in {"b":2,"a":5} inside or part of {"a":1, "b":2}

SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2,"a":5}'::jsonb ;
# ?column? 
# ----------
# f
# (1 row)

in rails

Book.where("? <@ payload", { publisher: nil }.to_json)
# Book Load (0.5ms) SELECT "books".* FROM "books" WHERE ('{"publisher":null}' <@ payload)
# => 
# id: 1,
# title: "book1",
# payload: {"publisher"=>nil},
# updated_at: Tue, 05 Apr 2022 23:20:11.788982000 UTC +00:00>]

?,?|, ?&:

here it checks if jsonb contains key or if the jsonb array contains the element as the element should be text ...
check these examples to understand it

Does the key d exist within the {"a":1, "b":2, "c":3}?

SELECT '{"a":1, "b":2, "c":3}'::jsonb ? 'd';
# ?column? 
# ----------
# f
# (1 row)

SELECT '{"a":1, "b":2, "c":3}'::jsonb ? 'a';
# ?column? 
# ----------
# t
# (1 row)

check if the element is inside jsonb array …
Does the element a exist within jsonb array ["a", "b"]?

SELECT '["a", "b"]'::jsonb ? 'a';
# ?column? 
# ----------
# t
# (1 row)

SELECT '["a", "b"]'::jsonb ? 'c';
# ?column? 
# ----------
# f
# (1 row)

Do keys b or c exist within {"a":1, "b":2, "c":3} ? Yes both are there

SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'];
# ?column? 
# ----------
# t
# (1 row)

Do keys a or d exist within {"a":1, "b":2, "c":3} ?
Yes a is there

SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['a', 'd'];
# ?column? 
# ----------
# t
# (1 row)

Do keys f or d exist within {"a":1, "b":2, "c":3} ? No both are not there

SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['f', 'd'];
# ?column? 
# ----------
# f
# (1 row)

same for elements in an array

SELECT '["a", "b"]'::jsonb ?| array['a', 'b'];
# ?column? 
# ----------
# t
# (1 row)

SELECT '["a", "b"]'::jsonb ?| array['a', 'c'];
# ?column? 
# ----------
# t
# (1 row)

SELECT '["a", "b"]'::jsonb ?| array['c', 'f'];
# ?column? 
# ----------
# f
# (1 row)

Do keys b and c exist within {"a":1, "b":2, "c":3} ? Yes both are there

SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'c'];
# ?column? 
# ----------
# t
# (1 row)

Do keys b and f exist within {"a":1, "b":2, "c":3} ? NO f does not exist

SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'f'];
# ?column? 
# ----------
# f
# (1 row)

Do keys f and d exist within {"a":1, "b":2, "c":3} ? No both are not there

SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['d', 'f'];
# ?column? 
# ----------
# f
# (1 row)

same for elements in an array

SELECT '["a", "b"]'::jsonb ?& array['a', 'b'];
# ?column? 
# ----------
# t
# (1 row)

SELECT '["a", "b"]'::jsonb ?& array['a', 'c'];
# ?column? 
# ----------
# f
# (1 row)

SELECT '["a", "b"]'::jsonb ?& array['d', 'c'];
# ?column? 
# ----------
# f
# (1 row)

Check keys in rails

Book.where("payload ? :key", key: 'authors').count
# (0.5ms) SELECT COUNT(*) FROM "books" WHERE (payload ? 'authors')
# => 2 

Book.where("payload ?| array[:keys]", keys: ['authors','publisher']).count
# (0.7ms) SELECT COUNT(*) FROM "books" WHERE (payload ?| array['authors','publisher'])
# => 3

Book.where("payload ?& array[:keys]", keys: ['authors','publisher']).count
# (0.6ms) SELECT COUNT(*) FROM "books" WHERE (payload ?&array['authors','publisher'])
# => 2

If you want to search for integer/float, you need to add explicit type casts like ::int and ::float :

Book.where("(payload #>> '{authors,0,id}')::int = :val", val: 1)
City.where("(payload -> 'geolocation' ->> 'latitude')::float = 
:val", val: 48.856613)

With aggregate functions

You can also use group by

SELECT
   MIN (CAST (payload ->> 'price' AS INTEGER)),
   MAX (CAST (payload ->> 'price' AS INTEGER)),
   SUM (CAST (payload ->> 'price' AS INTEGER)),
   AVG (CAST (payload ->> 'price' AS INTEGER))
FROM books;
 --this will give us
-- min | max | sum |         avg          
-------+-----+-----+----------------------
 --100 | 200 | 470 | 156.6666666666666667

More examples in SQL

SELECT
MIN (CAST (payload ->> 'price' AS INTEGER)),
MAX (CAST (payload ->> 'price' AS INTEGER)),
SUM (CAST (payload ->> 'price' AS INTEGER)),
AVG (CAST (payload ->> 'price' AS INTEGER))
FROM books
GROUP BY payload ->> 'publisher';
-- the first row is null
-- min | max | sum | avg 
-------+-----+-----+----------------------
-- | | | 
-- 100 | 100 | 100 | 100.0000000000000000
-- 100 | 100 | 100 | 100.0000000000000000
-- 170 | 200 | 740 | 185.0000000000000000
-- (4 rows)

Also more advanced examples in rails

Book.minimum("CAST (payload ->> 'price' AS INTEGER)")
# (0.6ms) SELECT MIN(CAST (payload ->> 'price' AS INTEGER)) FROM "books"
# => 100

Book.group("payload ->> 'publisher'").maximum("CAST (payload ->> 'price' AS INTEGER)")
# (0.6ms) SELECT MAX(CAST (payload ->> 'price' AS INTEGER)) AS maximum_cast_payload_price_as_integer, payload ->> 'publisher' AS payload_publisher FROM "books" GROUP BY payload ->> 'publisher'
# => {nil=>nil, "publisher1"=>100, "publisher2"=>200}

Book.group("payload ->> 'publisher'").sum("CAST (payload ->> 'price' AS INTEGER)")
# (0.8ms) SELECT SUM(CAST (payload ->> 'price' AS INTEGER)) AS sum_cast_payload_price_as_integer, payload ->> 'publisher' AS payload_publisher FROM "books" GROUP BY payload ->> 'publisher'
# => {nil=>0, "publisher1"=>100, "publisher2"=>370}

Book.group("payload ->> 'publisher'").average("CAST (payload ->> 'price' AS INTEGER)")
# (0.5ms) SELECT AVG(CAST (payload ->> 'price' AS INTEGER)) AS average_cast_payload_price_as_integer, payload ->> 'publisher' AS payload_publisher FROM "books" GROUP BY payload ->> 'publisher'
# => {nil=>nil, "publisher1"=>0.1e3, "publisher2"=>0.185e3}

Code snippets :

all code examples in jsonb series in my GitHub account 💻jsonb_posts

References:

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/
https://dev.to/kputra/rails-postgresql-jsonb-part-1-4ibg

Top comments (2)

Yaroslav Shmarov • Oct 30 '23

Thanks for sharing! It would be great to also give some more examples for searching all books with same publisher, all books that contain a same author

Francisco Quintero 🇨🇴 • Sep 22 '23

This is amazing. Thanks for sharing.

DEV Community

Insert and query jsonb in rails and postgres

Add column

Insert data

Show data

json and jsonb Operators

examples :

Additional jsonb only Operators

@> and <@ :

?,?|, ?&:

With aggregate functions

Code snippets :

References:

Top comments (2)

Read next

Benchmarking Crunchy Data for latency

The Prisma ORM: A Brief Overview and Introduction

When and Why to Use JSON Columns in SQL Databases

Simplifying SQL View Management in Laravel Migrations