How to work with postgresql JSON where keys are arbitrary?

Question

I have a JSON field c1 in a table t1 which is of form

{
 "11111" : { "STATUS" : "1"},
 "22222" : { "STATUS" : "0"},
 "33333" : { "STATUS" : "0"}
}

I want find those row of t1 in which any keys status is 1, I have tried below command

with r1 as(select t1.*, json_object_keys(c1) as keys from t1) select * from r1 where r1.c1->keys->>'STATUS' = '1';

but it is not giving me any row?

Clodoaldo Neto · Accepted Answer · 2014-02-12 10:41:10Z

2

select *
from t1
where exists (
    select 1
    from json_each(c1)
    where value ->> 'STATUS' = '1'
)

answered Feb 12, 2014 at 10:41

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1