postgresql - How to use operands for json in postgres -

i using postgres 9.4. how use regular operands such < , > <= etc json postgres key numeric, , value text till limit of key numeric value reached?

this table:

create table foo (   id numeric,   x json ); 

the values json follows:

 id | x ----+--------------------  1  | '{"1":"a","2":"b"}'  2  | '{"3":"c","4":"a"}'  3  | '{"5":"b","6":"c"}' 

so on randomly till key 100

i trying id, keys, values of json key key <= 20.

i have tried:

select *  foo  x->>'key' <='5'; 

the above query ran, , should have given me 20 rows of output, instead gave me 0. below query ran, , gave me 20 rows took on 30 mins!

select   id   , key::bigint key   , value::text value foo   , jsonb_each(x::jsonb) key::numeric <= 100; 

is there way use loop or do-while loop until x = 20 json? there way run time reduced?

any appreciated!

the operator can query json keys & use indexes on jsonb (but not on json) ? operator. unfortunately, cannot use in conjunction <=.

however, can use generate_series() if queried range relatively small:

-- use `jsonb` instead of `json` create table foo (   id numeric,   x jsonb );  -- sample data insert foo values (1, '{"1":"a","2":"b"}'),        (2, '{"3":"c","4":"a"}'),        (3, '{"5":"b","6":"c"}'),        (4, '{"7":"a","8":"b"}'),        (5, '{"9":"c","10":"a"}'),        (6, '{"11":"b","12":"c"}');  -- optionally index speed `?` queries create index foo_x_idx on foo using gin (x);  select distinct foo.*   generate_series(1, 5) s join   foo on x ? s::text; 

to work larger ranges, may need extract numeric keys of x integer array (int[]) & index that.