Question

HOW TO USE WHERE CLAUSE ON postgresql JSON DATA ?

Answer 1

--Let us take the sales table for using WHERE CLAUSE
 

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)  

--To find out what are the products  bought customer UMAR I use the following query
 

postgres=# SELECT sales_info ->>'customer' as customer_name,sales_info ->>'PRODUCTS' ->>'product_name' 
as name_of_product from sales where sales_info ->>'customer'='UMAR';
ERROR:  operator does not exist: text ->> unknown
LINE 1: ...stomer' as customer_name,sales_info ->>'PRODUCTS' ->>'produc...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.  

SOLUTION:

• FIRST TIME WE SPECIFIED sales_info ->>'PRODUCTS' ->>'product_name' SO WE NEED TO GIVE  sales_info ->'PRODUCTS' ->>'product_name' 

• postgres=# SELECT sales_info ->>'customer' as customer_name,sales_info ->'PRODUCTS' ->>'product_name' 
  as name_of_product from sales where sales_info ->>'customer'='UMAR';
   customer_name | name_of_product 
  ---------------+-----------------
   UMAR          | mobile
  (1 row) 

• Defaulty FROM above example First  sales_info -> 'PRODUCTS'  returns  as JSON objects. And then 'PRODUCTS' ->>'product_name' returns all products as text.
• when you call json nested data first you call like ->(json object key format)then Finally  you need to call ->>(text format or json object key format).
• if you specify firstly json field as text(->>) format when calling nested node ,after that if you specify -> or ->> values  json will not know first text format(->>) value that is why it throw error.
• JSON displayed  text format but actually json work as json object key(->) format .

• postgres=# SELECT sales_info ->>'customer' as customer_name,sales_info ->'PRODUCTS' ->'product_name' 
  as name_of_product from sales where sales_info ->>'customer'='UMAR';
   customer_name | name_of_product 
  ---------------+-----------------
   UMAR          | "mobile"
  (1 row) 

• HERE i specified first json object(->) format finnaly i called json object format (->) this is not a problem.
• other wise if you specify firstly as json text format (->>) finally if you give json object format (->) or  json text format (->>) what ever it is json will not know fist values surely it will throw error.