0 like 0 dislike
79 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4.9k points)  

--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.

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
1 answer
...