Using JSON Type

JSONB is a data type implemented in PostgreSQL aimed at enhancing the storage and manipulation of JSON data through improved efficiency and effectiveness when compared to the standard JSON data type.

JSONB stores JSON data in a binary form, enhancing indexing and query speed in comparison to the standard JSON data type. The utilization of binary format results in optimized storage and retrieval of JSON data, especially for handling extensive or intricate JSON entities.

Create JSONB Column

A table can be designed with a column assigned a data type of JSON or JSONB, similar to how columns are designated as Int, VARCHAR, or Double.

CREATE TABLE journal (
  id Int NOT NULL PRIMARY KEY, day VARCHAR, 
  diary_information JSONB
);

Insert JSON data

INSERT INTO journal (id, day, diary_information) 
VALUES 
  (
    1, “Tuesday”, '{"title": "My first day at work", "Feeling": "Nice place"}'
  );

JSON Functions and Operators

->: This operator allows you to extract a specific value from a JSON object, you specify the key as a “child” to the “parent”.

SELECT 
  Id, 
  day, 
  diary_information -> 'Feeling' AS Feeling 
FROM 
  journal;

->>: This operator allows you to extract a JSON object field as text without the quotes around it from a JSON object.

SELECT 
  id, 
  day, 
  dairy_information ->> 'Feeling' as Feeling 
FROM 
  products;