PostgreSQL Data Base with JSONB

Explanation

Create a table

To create a table you need to run:

CREATE TABLE "<table_name>" (<"id_column_name"> serial NOT NULL PRIMARY KEY, <"jsonb_column_name"> JSONB NOT NULL);

After creating a table you have to creat a index on the jsonb column (this is very advisable).

Create a gin index on the jsonb column

To create the gin index run:

CREATE INDEX <"index_name"> ON <"table"> USING gin(<"jsonb_column">);

Select on a jsonb table

To select on a jsonb table run:

SELECT <"json_path"> * FROM <"table">;

Insert in a jsonb table

To insert in a jsonb table run:

INSERT INTO <"table">(<"jsonb_column">) VALUES ('<"json_data">');

Delete in a jsonb table

To delete in a jsonb table run:

DELETE <"line"> FROM <"table_name">;

or

DELETE * FROM <"table"> WHERE <"predicat">;

Update json in a jsonb table

To update json data in a jsonb table run:

UPDATE <"table"> SET <"jsonb_column"> = jsonb_set(<"json_column">, '{<"jsonb_path">}'::text[], '<"modification">', false) WHERE <"predicat">;

Exemples

We want to make a database of person with pets. Persons have a last_name, first_name and age and pets have a name, breed and an owner_id.

We create 2 tables:

--  creation of the persons table
CREATE TABLE persons (id serial NOT NULL PRIMARY KEY, person JSONB NOT NULL);

-- creation of the pets table
CREATE TABLE pets (id serial NOT NULL PRIMARY KEY, pet JSONB NOT NULL);

We create 2 index on jsonb columns:

--  creation of the index on persons table
CREATE INDEX persons_index ON persons USING gin(person);

-- creation of the index on pets table
CREATE INDEX pets_index ON pets USING gin(pet);

Now we can insert 2 persons and 4 pets:

--  Insert persons
INSERT INTO persons(person) VALUES ('{"last_name" : "Bescheron", "first_name" : "Sarah", "age" : 26}'); -- id = 1
INSERT INTO persons(person) VALUES ('{"last_name" : "Cornet", "first_name" : "Roxane", "age" : 26}'); -- id = 2

-- We can insert several values at the same time by separating them with a coma
INSERT INTO persons(person) VALUES ('{"last_name" : "Bescheron", "first_name" : "Sarah", "age" : 26}'), ('{"last_name" : "Cornet", "first_name" : "Roxane", "age" : 26}');


-- Insert pets
INSERT INTO pets(pet) VALUES ('{"name" : "Mozart", "breed" : "dog", "owner_id": 2}'); -- id = 1
INSERT INTO pets(pet) VALUES ('{"name" : "Chopin", "breed" : "dog", "owner_id": 2}'); -- id = 2
INSERT INTO pets(pet) VALUES ('{"name" : "Gandalf", "breed" : "dog", "owner_id": 2}'); -- id = 3
INSERT INTO pets(pet) VALUES ('{"name" : "Jael", "breed" : "cat", "owner_id": 1}'); -- id = 4

Now we can request on the databse to get the all age of persons:

SELECT person->'age' FROM "persons";

Or all breep of pets:

SELECT pets->'breed' FROM pets;

Roxane has decided to change his last name, we will change that in our base:

UPDATE persons SET person = jsonb_set(person, '{last_name}'::text[], '"Mahiou"', false) WHERE id = 2;
Warning

We can make the where with the first name but if we have several people with the same first name we will change all last names. It can be useful if we want to modify many line in same time

UPDATE persons SET person = jsonb_set(person, '{age}'::text[], '27', false);

Unfortunately Gandalf went to save Mordor, we therefore decided to delete it from our database:

DELETE * FROM pets WHERE id = 3;

The complet exemple give:

CREATE TABLE persons (id serial NOT NULL PRIMARY KEY, person JSONB NOT NULL);
CREATE TABLE pets (id serial NOT NULL PRIMARY KEY, pet JSONB NOT NULL);
CREATE INDEX persons_index ON persons USING gin(person);
CREATE INDEX pets_index ON pets USING gin(pet);
INSERT INTO persons(person) VALUES ('{"last_name" : "Bescheron", "first_name" : "Sarah", "age" : 26}');
INSERT INTO persons(person) VALUES ('{"last_name" : "Cornet", "first_name" : "Roxane", "age" : 26}');
INSERT INTO pets(pet) VALUES ('{"name" : "Mozart", "breed" : "dog", "owner_id": 2}');
INSERT INTO pets(pet) VALUES ('{"name" : "Chopin", "breed" : "dog", "owner_id": 2}');
INSERT INTO pets(pet) VALUES ('{"name" : "Gandalf", "breed" : "dog", "owner_id": 2}');
INSERT INTO pets(pet) VALUES ('{"name" : "Jael", "breed" : "cat", "owner_id": 1}');
SELECT person->'age' FROM "persons";
SELECT pets->'breed' FROM pets;
UPDATE persons SET person = jsonb_set(person, '{last_name}'::text[], '"Mahiou"', false) WHERE id = 2;
UPDATE persons SET person = jsonb_set(person, '{age}'::text[], '27', false);
DELETE * FROM pets WHERE id = 3;