hselbie hselbie - 4 months ago 7
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

tags
, there are other entries as below but I want to return only the fields with
"{"tags":{"+good":true}}"
.

"{"tags":{"+good":true}}"
"{"has_temps":false,"tags":{"+good":true}}"
"{"tags":{"+good":true}}"
"{"has_temps":false,"too_long":true,"too_long_as_of":"2016-02-12T12:28:28.238+00:00","tags":{"+good":true}}"


I can get part of the way there with this statement in my
where
clause
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
"{"tags":{"+good":true}}"
only. So taking out the two entries that begin
has_temps
.

Any thoughts on how to do this?

Answer
with trips(metadata) as (
values
    ('{"tags":{"+good":true}}'),
    ('{"has_temps":false,"tags":{"+good":true}}'),
    ('{"tags":{"+good":true}}'),
    ('{"has_temps":false,"too_long":true,"too_long_as_of":"2016-02-12T12:28:28.238+00:00","tags":{"+good":true}}')
)
select *
from trips
where metadata = '{"tags":{"+good":true}}';

        metadata         
-------------------------
 {"tags":{"+good":true}}
 {"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}}';
Comments