DEV Community: Sara Alhaddadi

Ruby OOP review session (Part 1)

Sara Alhaddadi — Wed, 15 Mar 2023 20:49:01 +0000

let's review the main concepts in object oriented programming (OOP) in Ruby 📖 💎

In Ruby, everything is an object! anything that can be said to have a value is an object , that includes numbers, strings, arrays, and even classes and modules

'hi'.class # => String

1.class #=> Integer

Create class

class is a template of objects
a class definition is a region of code between the keywords class and end
class name : CamelCase naming convention as class/module name must be CONSTANT

class Employee
  # class code live here
end

Create objects (instantiation)

use new class method to create object

employee = Employee.new

`initialize` method

When you call new to create a new object, Ruby calls that object's initialize method as one step in create the object
any parameter passed to new will pass to initialize
this will help us to execute and code that we need in create object like set the state or data of these objects

class Employee
  def initialize
    puts "This object was initialized!"
  end
end

employee = Employee.new #=> This object was initialized!

Work with Instances

Instance Variables

store data of instance (something instance can have) as each instance has it is own version of this variable
it has the @ symbol in front of it
It is a variable that exists as long as the object instance exists
From outside the object, instance variables cannot be altered or even observed (i.e., ruby's instance variables are never public).
As with globals, instance variables have the nil value until they are initialized.

class Employee
  def initialize(name)
    @name = name
  end
end

employee1 = Employee.new("Sara")
employee2 = Employee.new("Khalid")

Instance Methods

define behavior of instance (something instance can do)
methods that objects can call

class Employee
  def initialize(name)
    @name = name
  end

  def say_hi
    "Hi, I am #{@name}"
  end
end

employee1 = Employee.new("Sara")
employee2 = Employee.new("Khalid")

employee1.say_hi # => "Hi, I am Sara"
employee2.say_hi # => "Hi, I am Khalid"

Accessor Methods

in the above example we can not access instance variable name outside the class.

employee1.name # => NoMethodError (undefined method `name' for #<Employee:0x0000561c18d842c8 @name="Sara">)

NoMethodError means that we called a method that doesn't exist or is unavailable to the object.
If we want to access the instance variable, we have to create a method that will deal with it.
getter method : method return the value (get)
setter method : method change the value (set), Ruby syntactical sugar to use def method_name=() that will help us to use assignment to call the setter method.
as a convention, Rubyists typically name those getter and setter methods using the same name as the instance variable.

class Employee
  def initialize(name)
    @name = name
  end

  def name
    @name
  end

  # normal method to set the value
  def set_name(new_name)
    @name = new_name
  end

  # syntactical sugar 
  def name=(new_name)
    @name = new_name
  end
end

employee1 = Employee.new("Sara")
employee2 = Employee.new("Khalid")

puts employee1.name # Sara
puts employee2.name # Khalid

employee1.set_name("new name")
puts employee1.name

employee2.name=("Omer")
puts employee2.name # Omer

employee2.name = "Ali"
puts employee2.name # Ali

Syntactical sugar setter methods always return the value that is passed in as an argument and ignore the return value of method, because We defined the value of the assignment as the value of the right hand expression, not the return value from the assigning method

class Employee
  def initialize(name)
    @name = name
  end

  def name
    @name
  end

  def name=(new_name)
    @name = new_name
    "Hi Sara this value will ignored"
  end
end

employee1 = Employee.new("Sara")

# call the method as assignments will not return the value from the method
# all other assignments evaluate to the right-hand side in Ruby
employee1.name = "Ali" # => "Ali"

# call the method with send will return the last value
employee1.public_send(:name=, "Ali") # => "Hi Sara this value will ignored"

attr_accessor , attr_reader ,attr_writer

help us define more getters and setters
attr_reader : getter method only allows you to retrieve the instance variable
attr_writer : setter method only allows you to set the instance variable
attr_accessor: method takes a symbol as an argument, which it uses to create the method name for the getter and setter methods

class Employee
  attr_accessor :name, :age

  def initialize(name, age)
    @name = name
    @age = age
  end
end

employee = Employee.new("Sara", 20)

employee.name = "Omer"
puts employee.name # Omer

employee.age = 15
puts employee.age # 15

removing the @ symbol, we're now calling the instance method, rather than the instance variable as it's generally a good idea because by this we can limit the access to these variables to certain conditions or logic

Work with classes

Class Methods

Class level methods, Class methods are methods we can call directly on the class itself, without having to instantiate any objects
defining a class method, we prepend the method name with the reserved word self or class name
use self is better as you do not need to change the methods name if you will change the class name

class Employee
  def Employee.what_am_i?
    "I am #{self} class"
  end

  def self.call
    "#{self} is calling: " + what_am_i?
  end
end

puts Employee.what_am_i? # I am Employee class

puts Employee.call  # Employee is calling: I am Employee class

Class Variables

Class level data shared between instances of classes
Class variables are created using two @ symbols like so: @@
same as instance variable we can not access class variable from outside the class

class Employee
  @@number_of_employees = 0

  def initialize
    @@number_of_employees += 1
    puts "Employee number #{@@number_of_employees} created successfully"
  end
end

Employee.new # Employee number 1 created successfully
Employee.new # Employee number 2 created successfully

puts Employee.number_of_employees # => NoMethodError (undefined method `number_of_employees' for Employee:Class)

Class reader / writer methods

same as instance reader and writer (getter and setter ) methods , just use self
no attr_accessor , attr_reader and attr_writer for class variables in ruby

class Employee
  @@number_of_employees = 0

  def initialize
    @@number_of_employees += 1
    puts "Employee number #{@@number_of_employees} created successfully"
  end

  def self.number_of_employees=(number)
    @@number_of_employees = number
  end

  def self.number_of_employees
    @@number_of_employees
  end
end

puts Employee.number_of_employees   # => 0

Employee.new # Employee number 1 created successfully
Employee.new #Employee number 2 created successfully

puts Employee.number_of_employees # => 2

puts Employee.number_of_employees = 0

puts Employee.number_of_employees # => 0

NOTE: you can define class level instance method using singleton class and class instance variables (Advanced you can read about it)

Understanding self

self which refers to the currently executing object, and changes depending on the scope it is used in
To make things clear, from within a class :

1- self, inside of an instance method, references the instance (object) that called the method - the calling object. Therefore, self.name= is the same as employee1.name=, in our example.

2- self, outside of an instance method, references the class and can be used to define class methods. Therefore if we define a class method, def self.number_of_employees=(n) is the same as def Employee.number_of_employees=(n).

class Employee
  puts "self is #{self}"

  def say
    puts "self is #{self}"
    puts "self class is #{self.class}"
  end
end
# self is Employee

but when we call the instance method

employee = Employee.new
employee.say
# self is #<Employee:0x000055d5e3a00b90>
# self class is Employee

use `self.name` VS `name` VS `@name` inside class:

1- self.name use self to force Ruby to work with instance methods like getter and setter
2- name in instance methods will look for local variables first then instance methods
3- @name will access the instance variable directly

in calling setter method you should use self to tell Ruby that you want to use the setter method of instance variable otherwise it will consider that you will set a local variable
the general rule from the Ruby style guide is to "Avoid self where not required." like inside an instance method to call getter, but it is good if we want to differentiate between instance variables and local variables with same names

class Employee
  attr_accessor :name, :age

  def initialize(name, age)
    @name = name
    @age = age
  end

  def set_local_age(value)
    # age here is local variable
    age = value
    # will use self to differentiate between age local variable and age instance variable
    puts "Local variable age: #{age}"
    puts "instance variable age: #{self.age}"
  end

  def set_instance_age(value)
    # we need to set the instance variable age, so use self
    self.age = value
    # for getter it is fine now as we did not have age local variable, so age = self.age =  getter method
    puts "instance variable age: #{age}"
    puts "instance variable age: #{self.age}"
  end

  def info
  # no need to use self
    self.age
  end
end

employee = Employee.new("Ali", 20)
puts employee.age # 20
puts employee.set_local_age(30)
# Local variable age: 30
# instance variable age: 20

puts employee.age # 20

puts employee.set_instance_age(30)
# instance variable age: 30
# instance variable age: 30

puts employee.age # 30

puts employee.info # 30

Method Access Control

public : can be called by anyone as there is no access control (default)
private : only accessible from inside the class in which it was defined (or one of its subclasses).
protected : are similar to private methods in that they cannot be invoked outside the class. The main difference between them is that protected methods can also be called from within other instances of the has the same method (from same class or subclass) , while private methods do not

private and protected instance methods

you can declare them by use public or private or protected above group of methods , as a line divider so the access to methods will be controlled by the nearest access control keyword above it
it is better to make code readable by use each access control only once in class and put all method under it, so you can have at most 3 groups. you can list all the public first and then the others

class Employee
  def initialize(age)
    @age = age
  end

  def work
    do_something
  end

  def older?(other)
    age > other.age
  end

  def other_do_something(other)
    other.do_something
  end

  private

  def do_something
    puts 'I am working'
  end

  protected

  attr_reader :age
end

ali = Employee.new(64)
ahmed = Employee.new(42)

ali.work # I am working

ali.do_something # => NoMethodError (private method `do_something' called for #<Employee:0x000055d9855bee10 @age=64>)
ali.other_do_something(ahmed) # => NoMethodError (private method `do_something' called for #<Employee:0x000056276856cad0 @age=42>)

ali.older?(ahmed)  # => true
ahmed.older?(ali)  # => false
ali.age # => NoMethodError (protected method `age' called for #<Employee:0x000055d9855bee10 @age=64>)

Alternatively, you can set access levels of named methods by listing them as arguments to the access control functions.

class Employee
  attr_reader :age

  def initialize(age)
    @age = age
  end

  def work
    do_something
  end

  def older?(other)
    age > other.age
  end

  def do_something
    puts 'I am working'
  end

  def other_do_something(other)
    other.do_something
  end

  protected :age
  private   :do_something
end
ali = Employee.new(64)
ahmed = Employee.new(42)

ali.work # I am working

ali.do_something # => NoMethodError (private method `do_something' called for #<Employee:0x0000561088ca9bf0 @age=64>)
ali.other_do_something(ahmed) # => NoMethodError (private method `do_something' called for #<Employee:0x0000561088ca9bf0 @age=42>)

ali.older?(ahmed)  # => true
ahmed.older?(ali)  # => false
ali.age # => NoMethodError (protected method `age' called for #<Employee:0x0000561088ca9bf0 @age=64>)

or you can use the access control before the method definition (inlined)

class Employee
  private def private_method
    do_something
  end
end

Employee.new.private_method # NoMethodError (private method `private_method' called for #<Employee:0x0000556fb1f57b90>)

private and protected class methods

The private and protected macros only affect instance methods of the current scope, not class methods.
for private class methods you can use private_class_method, we did not have one for protected as it is not often used
you can also use define private and protected class methods by open singleton class

class Employee
  def instance_method
    self.class.private_method
  end

  def self.public_method
    private_method
  end

  # will declare method access control after
  def self.private_method
    puts "Hi I am private"
  end

  private_class_method :private_method

  # inline
  private_class_method def self.private_method2
    puts "Hi I am private too"
  end
end

Employee.new.instance_method # NoMethodError (private method `private_method' called for Employee:Class)

Employee.public_method # Hi I am private

Employee.private_method # NoMethodError (private method `private_method' called for Employee:Class)

Employee.private_method2 # NoMethodError (private method `private_method2' called for Employee:Class)

References and more information:

Update & delete JSONB in rails and postgres

Sara Alhaddadi — Fri, 05 Aug 2022 19:01:09 +0000

Welcome back to learn more about jsonb ...✨✨

After a little digging into jsonb and getting to know it and how to add and query data, let's dive in more to learn how to modify it...

Update JSONB

In rails work with it as hash

> book.payload['publisher'] = "new publisher"
> book.save
> book.reload.payload['publisher']
 => "new publisher"
> new_payload = {publisher: "Blue Ocean"}
> book.update(payload: new_payload)
> book.reload.payload
 => {"publisher"=>"Blue Ocean"}
> book.update(payload: {})

but deal with jsonb as hashes to grape the data and change it then save or update each record is not feasible or functional!

if we need to update so much records or add keys to all records … we really need some postgres tools to help here ….

so let’s ask postgres how we can do that in it ?

Postgres : sure use my update statement with some of my operators and functions

Use update statement can be trickery … so if we try something like

UPDATE books SET payload -> 'publisher' = 'sara'
 WHERE title = 'book1';
-- we will get error
ERROR:  syntax error at or near "->"
LINE 1: UPDATE books SET payload -> 'publisher' = 'sara' WHERE title…

so actually let’s start from the beginning and from the fact that payload as whole is table column and in database

so we can use update to change the whole value of whole column not to change parts of it …

so if we want to update or delete something from jsonb column ( like payload here ) we should replace the whole payload with new one and save it to database and this is what we will discover now ...

Update & insert keys in postgres

jsonb_set is function that allow you to change the value of specific key and return new version of jsonb column that we can use in update statement to replace the old value

as from Postgres

Function	Return Type	Description	Example	Example Result
jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])	jsonb	Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays.	jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)	jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')

first to explain jsonb_set I will use it in SELECT statement to see the return value from it

in [{"f1":1,"f2":null},2] jsonb array replace the value of path {0,f3} which mean as we saw before that the value of key f3 inside first element – 0 index - {"f1":1,"f2":null}

here in below example we want to change the value of that f3 by [2,3,4] and here the create_missing params does not pass to function so we will use the default value which true mean create f3 if it does not exists and then return the new version of the whole jsonb after update

SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
-- jsonb_set 
-- ---------------------------------------------
-- [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-- (1 row)

but here in create_missing we pass (false) which means if you did not find f3 do not create it (update only)

SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]',false);
-- jsonb_set 
-- ----------------------------
-- [{"f1": 1, "f2": null}, 2]
-- (1 row)

here we did not find f3 so the return value will be the original value ( on change )

let's learn more by these examples ...

Yes, more examples as you know...
If you've been following the series, you'll know that I use a lot of examples as I think it's a great way to discover and examine new information.

here we use -1 as index which mean the last element in array (negative integers that appear in path count from the end )

SELECT jsonb_set('[1,2,{"f1":1,"f2":null}]', '{-1,f2}','[2,3,4]',false);
-- jsonb_set 
-- ------------------------------------
-- [1, 2, {"f1": 1, "f2": [2, 3, 4]}]
-- (1 row)

SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0}','[2,3,4]',false);
-- jsonb_set 
-- ----------------
-- [[2, 3, 4], 2]
-- (1 row)

what if we want to change the value of publisher in our book example … here I use SELECT just to show the new value of payload

SELECT jsonb_set(payload, '{publisher}', '"new publisher"') from books WHERE title = 'book1';
-- {"price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}

now let’s really update the value of publisher in book1 as it is new publisher now!

UPDATE books SET payload = jsonb_set(payload, '{publisher}', '"new publisher"') WHERE title = 'book1';

take more complicated examples with `tags`

Add new key tags or update it if it is exists

UPDATE books SET payload = jsonb_set(payload, '{tags}', '["tag3", "tag4"]') WHERE title = 'book1';
-- {"tags": ["tag3", "tag4"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}

Replacing the second tag (0-indexed):

UPDATE books SET payload = jsonb_set(payload, '{tags,1}', '"tag5"') WHERE title = 'book1';
-- {"tags": ["tag3", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}

Delete key from JSONB

jsonb_set is really great tool to update or add new keys to jsonb … but what about delete or concatenate values ?
Fortunately we have some nice jsonb operators to do that for us
take a look to them in this table from postgres documentation

Operator	Right Operand Type	Description	Example
\|\|	jsonb	Concatenate two jsonb values into a new jsonb value	'["a", "b"]'::jsonb \|\| '["c", "d"]'::jsonb
-	text	Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key .	'{"a": "b"}'::jsonb - 'a'
-	integer	Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.	'["a", "b"]'::jsonb - 1
#-	text[]	Delete the field or element with specified path (for JSON arrays, negative integers count from the end)	'["a", {"b":1}]'::jsonb #- '{1,b}'

As before I find it is handy to explain it first by using SELECT to show the final result of our jsonb

||

the two pipe || use concatenate jsonbs objects or arrays

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

it will not remove duplicates in jsonb arrays

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

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

but in jsonb object as we sow in first tutorial of this series that jsonb replace duplicate keys with last value

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

–

to delete key use mins – with string of key in jsonb object

SELECT '{"a": "b"}'::jsonb - 'a';
-- ?column? 
-- ----------
-- {}
-- (1 row)

it is ok if key does not exists as no error will be raisen … cool and handy !

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

to delete element use mins – with number of index or string element in jsonb array

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

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

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

we cannot delete from object using integer index if integer is key in jsonb it should be string

SELECT '{"1":1, "2":2}'::jsonb - 1;
ERROR:  cannot delete from object using integer index

SELECT '{"1":1, "2":2}'::jsonb - '1';
 ?column? 
----------
 {"2": 2}
(1 row)

#-

what if we want to delete field or element with specified path … sure we know to use – to delete and for select path we can use # which indicate that we will use path - as we see in #> and #>> -

so let’s combine the functionality of # and – to get #- which will delete field or element with specified path

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

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

as I said before it is ok if it does not find the element in path no errors will annoy you

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

let’s work with tags in book … with this examples
this is the current value of payload for one book

{"tags": ["tag3", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}

and we will show changes to it in these examples after each update statement

Remove the last tag:

UPDATE books SET payload = payload #- '{tags,-1}' WHERE title = 'book1';
-- {"tags": ["tag3"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}

Complex update (delete the last tag, insert a new tag, and change the name or insert it if it is not there):
- delete the last tag payload #- '{tags,-1}'
- insert a new tag jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true)
- change the name or insert it jsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"')

UPDATE books SET payload = jsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"') WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}

more interesting examples to to practice ...

UPDATE books SET payload = jsonb_set(payload, '{tags,1}', '"tag5"') WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}

UPDATE books SET payload = payload || '{"a": "apple"}' WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}

here notice that I use select not update . So the original value ( which from the last update statement ) will not change after each example

SELECT payload - 'a' from books WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}

SELECT (payload -> 'tags') - 'tag10' from books WHERE title = 'book1';
-- ["tag5"]

-- tags are "tags": ["tag10", "tag5"] the proives query is select not update
SELECT (payload -> 'tags') - 0 from books WHERE title = 'book1';
-- ["tag5"]

SELECT payload #- '{"tags",0}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}

SELECT payload #- '{"authors",0,"name"}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}

-- no error if key is not found
SELECT payload #- '{"authors",0,"age"}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}

Code snippets :

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

References

https://aaronbos.dev/posts/update-json-postgresql

Insert and query jsonb in rails and postgres

Sara Alhaddadi — Fri, 05 Aug 2022 17:12:50 +0000

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

JSON Primitive Types and Corresponding PostgreSQL Types

Sara Alhaddadi — Tue, 02 Aug 2022 19:36:00 +0000

How does PostgreSQL store data inside jsonb and map them to its data type?
first Postgres has a special data type for JSON it is different from PostgreSQL data types _-like text, numeric and so on …- _and we have different methods to check those types for PostgreSQL type we use pg_typeof, and for jsonb type, we use jsonb_typeof

Remember that in JSON format

keys should be strings
values can be strings, numbers, boolean, null, array, or another JSON object

this table from Postgrse documentation
summarize the main JSON data type in Postgres

JSON type	PostgreSQL type	Notes
string	text	\u0000 is disallowed, as are Unicode escapes representing characters not available in the database encoding
number	numeric	NaN and infinity values are disallowed
boolean	boolean	Only lowercase true and false spellings are accepted
null	(none)	SQL NULL is a different concept

let’s dive and explain each JSON type in PostgreSQL

jsonb string:

you can store any text value except \u0000 - which a Unicode escapes representing characters that represent NULL

to show the meaning of Unicode characters in Postgres use E in front of text like

----------------------- jsonb string ---------------------------
select E'\u0001' as value;
-- value 
-- -------
-- \x01
-- (1 row)

select E'\u0000' as value;
-- ERROR: invalid Unicode escape value at or near "E'\u0000"
-- LINE 1: select E'\u0000' as value;

SELECT '1234\u0000';
-- ?column? 
-- ------------
-- 1234\u0000
-- (1 row)

SELECT E'My star \u2B50';
--  ?column?  
-- -----------
--  My star ⭐
-- (1 row)

SELECT E'1234\u0000';
-- ERROR: invalid Unicode escape value at or near "E'1234\u0000"
-- LINE 1: SELECT E'1234\u0000';

SELECT '"1234\u0000"'::jsonb;
-- ERROR: unsupported Unicode escape sequence
-- LINE 1: SELECT '"1234\u0000"'::jsonb;
-- ^
-- DETAIL: \u0000 cannot be converted to text.
-- CONTEXT: JSON data, line 1: ...

SELECT '"My face \u2B50"'::jsonb;
--     jsonb    
-- -------------
--  "My face ⭐"
-- (1 row)

jsonb number :

you can store any positive or negative numbers just except NaN and infinity

----------------------- jsonb number ---------------------------
SELECT jsonb_typeof('1'::jsonb);
-- jsonb_typeof 
-- --------------
-- number
-- (1 row)

SELECT jsonb_typeof('-1'::jsonb);
-- jsonb_typeof 
-- --------------
-- number
-- (1 row)

infinity: can represent like infinity,- infinity,inf, -inf

SELECT jsonb_typeof('inf'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('inf'::jsonb);
-- ^
-- DETAIL: Token "inf" is invalid.
-- CONTEXT: JSON data, line 1: inf

SELECT jsonb_typeof('-inf'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('-inf'::jsonb);
-- ^
-- DETAIL: Token "-inf" is invalid.
-- CONTEXT: JSON data, line 1: -inf

NAN: (not a number) value is used to represent undefined calculational results like

select 'infinity'::float / 'infinity'::float;
-- ?column? 
-- ----------
-- NaN
-- (1 row)

SELECT jsonb_typeof('NAN'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('NAN'::jsonb);
-- ^
-- DETAIL: Token "NAN" is invalid.
-- CONTEXT: JSON data, line 1: NAN

jsonb boolean:

Only lowercase true and false are considered as boolean

SELECT 'true'::jsonb;
-- jsonb 
-- -------
-- true
-- (1 row)

SELECT jsonb_typeof('true'::jsonb);
-- jsonb_typeof 
-- --------------
-- boolean
-- (1 row)

SELECT jsonb_typeof('false'::jsonb);
-- jsonb_typeof 
-- --------------
-- boolean
-- (1 row)

SELECT jsonb_typeof('False'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('False'::jsonb);
-- ^
-- DETAIL: Token "False" is invalid.
-- CONTEXT: JSON data, line 1: False

SELECT jsonb_typeof('True'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('True'::jsonb);
-- ^
-- DETAIL: Token "True" is invalid.
-- CONTEXT: JSON data, line 1: True

SELECT jsonb_typeof('"True"'::jsonb);
-- jsonb_typeof 
-- --------------
-- string
-- (1 row)

null and Null in jsonb and Postgres:

jsonb null is different from NULL in Postgres as SQL’s null means “of unknown value” and JSON’s null means “empty/no value”.

let’s take some examples to explain this:

Note : use -> to return the value of key in jsonb as jsonb type and ->> convert the value of key as text and return text. . By the way I explained this in next post in this series

select '{"a": 1, "b": null}'::jsonb->'c';
-- ?column? 
-- ----------
-- (1 row)

c is not keys in jsonb so if we ask for it is value Postgres will search for it in '{"a": 1, "b": null}' and as it can not find it so Postgres does not know the value of c so it says it is unknown value means it is NULL, as c does not there

Note : in select Null does not shown

select '{"a": 1, "b": null}'::jsonb->'c' IS NULL;
-- ?column? 
-- ----------
-- t
-- (1 row)

but what about b ?! what’s the value of b?

select '{"a": 1, "b": null}'::jsonb->'b';
-- ?column? 
-- ----------
-- null
-- (1 row)

here b is key inside jsonb and it has value but it is null ( empty/no value ) so if Postgres look for b it can find it and return its value as b not NULL because it has value and it does not with unknown value as Postgres really can find it and knows its value

select '{"a": 1, "b": null}'::jsonb->'b' IS NULL;
-- ?column? 
-- ----------
-- f
-- (1 row)

notice that in previous examples we use -> which is used to return the value of key in jsonb as it is but what if we use ->> that will convert it to text … things become tricky here look to these examples

select '{"a": 1, "b": null}'::jsonb->>'b' IS Null;
-- ?column? 
-- ----------
-- t
-- (1 row)

select '{"a": 1, "b": null}'::jsonb->>'c' IS Null;
-- ?column? 
-- ----------
-- t
-- (1 row)

now the value of c and b IS NULL why?
Here explain from PostgreSQL

One of the design principles of PostgreSQL, however, is that casting anything to text should give something parsable back to the original value (whenever possible).

So if we will convert null to text it will be NULL as it is the nearest value to it that we can use to re-parse it back …

but why we can not just convert null to be ‘null’ as string with 2 single quotes ??!!

doing something like that will make confused between ‘null’string value that stored as strung in keys like

select '{"a": 1, "b": null}'::jsonb->>'b';
-- ?column? 
-- ----------
-- (1 row)

select '{"a": 1, "b": "null"}'::jsonb->>'b';
-- ?column? 
-- ----------
-- null
-- (1 row)

select pg_typeof('{"a": 1, "b": "null"}'::jsonb->>'b');
-- pg_typeof 
-- -----------
-- text
-- (1 row)

select pg_typeof('{"a": 1, "b": null}'::jsonb->>'b');
-- pg_typeof 
-- -----------
-- text
-- (1 row)

so ‘null’ to text = ‘null’ and if you re-parse ‘null’ to original type it will string ‘null’

but null to text = NULL and if you re-parse null to original type it will null

this lead us to golden point here so if you want to check that if key does not there or its value is null use ->> to get value of that key and check if IS NULL ….

same if you want to say if value is there for keys this means the key is there or its value is not null use ->> to get value of that key and check if IS NOT NULL

Reference : Check this for more details
PostgreSQL_and_null_value

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

Brief introduction to jsonb in PostgreSQL

Sara Alhaddadi — Mon, 01 Aug 2022 18:39:00 +0000

When working with databases especially relational ones we always consider looking to our data as structured data that can fit inside rows and columns and follow the database constraints ... so we do our best to have a great structural design in our databases

but what if we find ourselves in a situation where we do not have structured data ... we try to make it structural but we can not or it is not feasible to do that ... situations when we really need to store and manage non-relational data like JSON inside a relational database!

Fortunately, modern relational databases like Postgres support that ...

before deep diving in use JSON in postgres let's give some introduction
information about what we are trying to do here ...

What is JSON ?

JSON stands for javascript object notation – the way that JavaScript use to represent its object
it is an open and wide standard format that consists of key-value pairs
defined by curly braces hold key-value pairs (object ) each key is followed by :(colon), and pairs are separated by, (comma).
value can be Number, strings, boolean, null, array, or other JSON object

something like this :

{
  "tags": [
    "tag3",
    "tag4"
  ],
  "price": 100,
  "authors": [
    {
      "id": 1,
      "name": "author1"
    },
    {
      "id": 2,
      "name": "author2"
    }
  ],
  "publisher": "publisher1",
  "published_date": "2017-04-07"
}

If you want you can learn more about JSON from json_quick_guide

When using JSON in databases ?

As I mention before the idea is “I want to store and manage data as JSON inside a relational database” … but why do we want that ??! And when use JSON in databases??

First of all please keep this common and important advice in your mind

Do not use it because you can … use it because you really need it and know what you are really doing

Here are some examples of when you might really need to use JSON in databases :

Store non-relational data (like hierarchy or not structured you can not put it in the same shape). Examples of extra data, settings, configurations,...
Unpredictable arbitrary data structure e.g. user add tags with different values
Lots of attributes that are rarely used or it is in only some records
Comes as JSON, as response comes from external data sources
Data with dynamic values
Schema flexibility as attributes can be added or removed in the JSON fields without having to change the DB (Database) schema
Nested objects. If your data set has nested objects (single or multi-level)

Good use cases

Here are some good use cases for using JSON in databases:

Maintain data that comes from an external service in the same structure and format (as JSON) that it arrived to you as
Generate JSON response from DB directly to avoid transforming data before returning it via your JSON API because building deeply nested and has multiple arrays data in real-time on every request would be incredibly taxing on the system also when working with micro-services that depend on each other.
In agile when a requirement is not clear we can start with JSON and then when we have a good idea about the system and DB design commit them to relational tables as needed

Postgresql & json :

Postgresql actually is a relational database but you can easily integrate relational and non-relation data as PostgreSQL care about JSON and supports it in different data types as it has json,jsonb and jsonpath :

In version 9.2 PostgreSQL support json data type
In version 9.4 PostgreSQL support jsonb and it provides more functions and operators for manipulating json data.
In version 12 PostgreSQL support sql/json standard and jsonpath queries

Here we will focus on jsonb only ….

json vs jsonb

from the Postgres docs themselves:

“The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.”

json is faster at writing, but jsonb is faster at reading.
As a general rule, your app will read more often than it writes.

In most cases, JSONB is what you should be using

JsonB stands for binary json as it is json stored in binary format…

and to make these differences between them clearer take this example about how each type deal with data

SELECT '{"c":0, "a":2,"a":1,"d":3,"b":5}'::json, '{"c":0, "a":2,"a":1,"d":"3","b":5}'::jsonb;
-- json | jsonb 
-- ------------------------------------+------------------------------------
-- {"c":0, "a":2,"a":1,"d":3,"b":5} | {"a": 1, "b": 5, "c": 0, "d": "3"}
-- (1 row)

as you can see that json data type saves the same input without parsing it so without order keys , or remove duplicated key ‘a’, but in jsonb we parse the input data so we will reorder keys and remove duplicated key...
this table summarize main difference between them ...

json	jsonb
stores an exact copy of the text input, which must be reparsed again	stores a binary representation that avoids reparsing the data structure
keep duplicate keys.	remove duplicate keys.
It doesn’t support indexes	It supports indexing
will re-parse the stored value every time; that means that key ordering will be same from the input	content is stored in binary format and no key ordering is guaranteed.
performed a sequential scan, also remember that the JSON content is going to be reparsed for each record, which can be slow for complex structures.	The search is not as optimized as it would be with an expression index on the path you’re querying, but still uses the index.

Drawbacks of using JSON in database:

As I mentioned in advice about do not use JSON in the database until you know what you are really doing …
as work with these features becomes more tricky over time and rather than improve your system you may destroy it !!

Here are some drawbacks of using jsonb in db (database) :

Larger storage : jsonb storage results in a larger storage footprint , jsonb storage does not deduplicate the key names in the json, I find some people try to solve this by

use more compact names (unix-style) instead of more descriptive names. For example, if you’re storing millions of instances of a particular key, it would be better storage-wise to name it “pb” instead of “publishername”
.

but I think this is not a good idea as We can forget things very quickly and I think remembering the meaning of this abbreviation “pb” will be difficult in the coming months and can cause a lot of confusion for new developers.

No statistics : postresql does not store column statistics for jsonb columns. When you store your data as json blobs you deny the query planner the ability to make sensible decisions based on table and column statistics
Brake normalization : from a design point of view, it makes the database denormalized .the 1nf is not even satisfied, as values are not atomic with respect to the DBMS.
No constraints : yes no clear constraints and no grantees in type and consistent of data … e.g. if you store FK inside json like this no grantees that these FK are for existing records.
Week indexing : you lose most of the indexing features
Tricky query : it becomes very tricky to make sure the query will work correctly in future like key might not exist and data types can be in different data type (dynamic values ) from a row to another. what if someone store string in key that expected to store date !!

By the way this is only and introduction about the topic, in next toturails of this series we will learn and practice more about jsonb in rails and postgres ... Be around!🔥

Most information in this post is summized from these great references, take a look if you want to read more about these concepts ...

references

https://www.postgresql.org/docs/current/datatype-json.html
http://www.thomascerqueus.fr/json-fields-relational-databases-postgresql/
https://www.youtube.com/watch?v=2dQjfdXxtJw
https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/
https://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails
https://youtu.be/p9RItyeKbLQ

DEV Community: Sara Alhaddadi

Ruby OOP review session (Part 1)

Create class

Create objects (instantiation)

initialize method

Work with Instances

Instance Variables

Instance Methods

Accessor Methods

attr_accessor , attr_reader ,attr_writer

Work with classes

Class Methods

Class Variables

Class reader / writer methods

Understanding self

use self.name VS name VS @name inside class:

Method Access Control

private and protected instance methods

private and protected class methods

References and more information:

Update & delete JSONB in rails and postgres

Update JSONB

Update & insert keys in postgres

take more complicated examples with tags

Delete key from JSONB

||

–

#-

Code snippets :

References

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:

JSON Primitive Types and Corresponding PostgreSQL Types

Remember that in JSON format

jsonb string:

jsonb number :

jsonb boolean:

null and Null in jsonb and Postgres:

Brief introduction to jsonb in PostgreSQL

What is JSON ?

When using JSON in databases ?

Good use cases

Postgresql & json :

json vs jsonb

Drawbacks of using JSON in database:

references

`initialize` method

use `self.name` VS `name` VS `@name` inside class:

take more complicated examples with `tags`