How can I compare json field with a string value in postgresql?-CodePudding

I have a field payload saved in a postgresql table which is json type. This type has a nested field subcategory which is string. Below is the output of this value:

=> select payload->'subcategory' from "Merchant";
           ?column?
-------------------------------
 "Food"
 null
 "AUTOMOTIVE"
 null
 "MEDICAL"
 null
 null
 "CLUB"
 "Petrol Stations"

However, I can't put this field in the where clause. Below query returns 0 rows. But from above output it shows there are rows whose value is CLUB. What is the right way to use json field in where clause?

=> select count(*) from "Merchant" where ("payload"->'subcategory')::text = 'CLUB';
 count
-------
     0

CodePudding user response：

Figured out what's wrong, I need to use ->> in the where like "payload"->'subcategory'.

that because ->> converts it to text while -> makes it as JSONB

CodePudding user response：

An alternative solution is to use the JSON contains operator @>:

select count(*) 
from "Merchant" 
where payload @> '{"subcategory": "CLUB")';