hselbie hselbie - 1 year ago 36
JSON Question

Get all entries for a specific json tag only in postgresql

I have a database with a json field which has multiple parts including one called

, there are other entries as below but I want to return only the fields with


I can get part of the way there with this statement in my
trips.metadata->'tags'->>'+good' = 'true'
but that returns all instances where tags are good and true including all entries above. I want to return entries with the specific statement
only. So taking out the two entries that begin

Any thoughts on how to do this?

Answer Source
with trips(metadata) as (
select *
from trips
where metadata = '{"tags":{"+good":true}}';

(2 rows)

If the column's type is json then you should cast it to jsonb:

where metadata::jsonb = '{"tags":{"+good":true}}';