Question

HOW TO USE FUNCTION ON postgresql JSON DATA

Answer 1

json_each() function allows us to expand the outermost JSON object into a set of key-value pairs. 
 

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)  

--To get a set of keys in the outermost JSON object use function json_object_keys function
 

postgres=# select json_each (sales_info) from sales;
                            json_each                            
-----------------------------------------------------------------
 (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}")
(6 rows)  

--If you want calculate  array size of json function
 

select json_array_length('[1,2,3,4,5,6,7,8]');