Insert and query jsonb in rails and postgres
Tags: rails, jsonb, postgres, sql
Published: 2022-08-05 17:12:50

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
```sql
CREATE TABLE cards (
  id integer NOT NULL,
 board_id integer 
  data jsonb
);
```
or in rails migration
```ruby
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
```ruby
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.
```sql
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**
```ruby
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**.
```ruby
> 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](https://www.postgresql.org/docs/9.5/functions-json.html#FUNCTIONS-JSON-OP-TABLE) 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

```sql
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
```ruby
=> 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
```sql
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_
```ruby
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 
```ruby
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
```sql
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)
```
```ruby
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](https://www.postgresql.org/docs/9.5/functions-json.html#FUNCTIONS-JSONB-OP-TABLE)
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|'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']|
|?&|text[]|Do all of these key/element strings exist?|'["a", "b"]'::jsonb ?& array['a', 'b']|

examples :
###@> and <@ :
```sql
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}
```sql
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2,"a":5}'::jsonb ;
# ?column? 
# ----------
# f
# (1 row)
```
in rails
```ruby
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}`?
```sql
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"]`?
```sql
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
```sql
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
```sql
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
```sql
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['f', 'd'];
# ?column? 
# ----------
# f
# (1 row)
```
same for elements in an array
```sql
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
```sql
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 
```sql
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
```sql
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?& array['d', 'f'];
# ?column? 
# ----------
# f
# (1 row)
```
same for elements in an array
```sql
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 
```ruby
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` :
```ruby
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
```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;
 --this will give us
-- min | max | sum |         avg          
-------+-----+-----+----------------------
 --100 | 200 | 470 | 156.6666666666666667
```
More examples in SQL
```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 
```ruby
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](https://github.com/SaraAlhaddadi/jsonb_posts)

---

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