what is JSON ?
- It is one of the data type in postgres.
- Json + PostgreSQL =NoSQL
- JSON STANDS FOR JavaScript Object Notation
- JSON data types stored value is valid according to the JSON rules.
- JSON data types are for storing JSON data as multi-level, dynamically structured object graphs.
- serialised object is stored in a text column. The json type takes care of deserialising it back to object graph while reading values from that column.
- The main usage of JSON is to transport data between a server and web application. Unlike other formats, JSON is human-readable text.
- PostgreSQL supports JSON data type since version 9.2.
- It provides many functions and operators for manipulating JSON data.
There are two JSON data types:
1.json
2.jsonb
- The json data type stores an exact copy of the input text.
- jsonb data is stored in a decomposed binary format
- jsonb Insertion makes it slightly slower to input due to added conversion overhead.
- jsonb also supports indexing
- JSONB does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.
PRACTICAL 1. CREATING SIMPLE JSON TABLE:
postgres=# CREATE TABLE SALES (
ID INT NOT NULL PRIMARY KEY,
SALES_INFO json NOT NULL
);
From above Sales table consists of two columns:
- The id column is the primary key column that identifies the sales id.
- The sales_info column stores the data in the form of JSON data types.
--Describe the sales table using \d
postgres=# \d sales
Table "public.sales"
Column | Type | Modifiers
------------+---------+-----------
id | integer | not null
sales_info | json | not null
Indexes:
"sales_pkey" PRIMARY KEY, btree (id)
PRACTICAL 2. INSERTING JSON DATA ON SALES TABLE:
INSERT INTO SALES VALUES
(1,'{ "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}'
);
INSERT INTO SALES VALUES
(2,'{ "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}'
);
INSERT INTO SALES VALUES
(3,'{ "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}'
);
--List down the sales table
postgres=# select * from sales;
id | sales_info
----+----------------------------------------------------------------------------------
1 | { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}
2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
(3 rows)
--list down the json column only
postgres=# select sales_info from sales;
sales_info
----------------------------------------------------------------------------------
{ "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}
{ "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
{ "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
(3 rows)
--List down the id column only for understanding purposes.
postgres=# select id from sales;
id
----
1
2
3
(3 rows)
Deleting json objects:
postgres=# select * from sales;
id | sales_info
----+-------------------------------------------------------------------------------
2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
4 | { "customer": "junaith", "PRODUCTS": {"product_name": "pen","total_item": 8}}
7 | { "customer": "daniel", "PRODUCTS": {"product_name": "car","total_item": 8}}
8 | { "customer": "daniel", "PRODUCTS": {"product_name": "car","total_item": 8}}
(5 rows)
postgres=# delete from sales where sales_info ->'PRODUCTS'->>'total_item'='8';
DELETE 3
postgres=# select * from sales;
id | sales_info
----+-------------------------------------------------------------------------------
2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
(2 rows)
PRACTICAL 3.HOW TO QUERYING & FILTERING JSON DATA:
the two operators -> and ->> to help you query JSON data.
- -> will return the attribute as a JSON object key(original JSON type).
- ->> will return the property as integer or text (the parsed form of the attribute).
--See the below example first two query did'nt specify column name that is why it showing ?column? after that i specified the column name as customer_name
postgres=# select sales_info -> 'customer' from sales;
?column?
----------
"NIJAM"
"ABU"
"UMAR"
(3 rows)
postgres=# select sales_info ->> 'customer' from sales;
?column?
----------
NIJAM
ABU
UMAR
(3 rows)
postgres=# select sales_info ->> 'customer' as customer_name from sales;
customer_name
---------------
NIJAM
ABU
UMAR
(3 rows)
postgres=# select sales_info -> 'PRODUCTS' from sales;
?column?
----------------------------------------------
{"product_name": "choclate","total_item": 6}
{"product_name": "badam","total_item": 5}
{"product_name": "mobile","total_item": 1}
- Using -> operator returns a JSON object, you can chain it with the operator ->> to retrieve a specific node.
- JSON IS CASESENSITIVE DEFAULTY, from below example first i try to retrieve the data using "products"(lowercase) that is why it showed empty value after that i chaneged "PRODUCTS"(UPPER CASE) then it showing values.
postgres=# select sales_info -> 'products' ->>'product_name' from sales;
?column?
----------
(3 rows)
postgres=# select sales_info -> 'PRODUCTS' ->'product_name' from sales;
?column?
------------
"choclate"
"badam"
"mobile"
(3 rows)
postgres=# select sales_info -> 'PRODUCTS' ->>'product_name' from sales;
?column?
----------
choclate
badam
mobile
(3 rows
FROM above example First sales_info -> 'PRODUCTS' returns as JSON objects. And then sales_info -> 'PRODUCTS' ->>'product_name' returns all products as text.
More information https://www.tutorialdba.com/p/postgresql-json-data-type.html?m=1