DEV Community

Cover image for Insert and query jsonb in rails and postgres
Sara Alhaddadi
Sara Alhaddadi

Posted on

Insert and query jsonb in rails and postgres

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

or in rails migration

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

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

add_column :users, :data_jsonb, :jsonb
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

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

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

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

same as

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

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)
Enter fullscreen mode Exit fullscreen mode
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)
# => [] 
Enter fullscreen mode Exit fullscreen mode

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

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

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

?,?|, ?&:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

Collapse
 
superails profile image
Yaroslav Shmarov

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

Collapse
 
cescquintero profile image
Francisco Quintero πŸ‡¨πŸ‡΄

This is amazing. Thanks for sharing.