Get all entries for a specific json tag only in postgresql

Question

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 1

With jsonb column the solution is obvious:

with trips(metadata) as (
values
    ('{"tags":{"+good":true}}'::jsonb),
    ('{"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}}';