0 like 0 dislike
118 views
asked by (4.9k points)  
What is PostgreSQL Json Data Type ? And how to handle & perform​ insert,up upda, delete

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4.9k points)  
 
Best answer

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

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
...