CODEWITHSUNDEEP
jsonpostgresqloperands
wizengeeky
wizengeeky

Reputation: 67

How to use operands for json in postgres

I am using postgres 9.4. How to use regular operands such as < , > <= etc with json postgres where key is a numeric, and value is a text till a limit of key numeric value is reached?

This is my table:

create table foo (
  id numeric,
  x json
);

The values for the json are as follows:

 id | x
----+--------------------
 1  | '{"1":"A","2":"B"}'
 2  | '{"3":"C","4":"A"}'
 3  | '{"5":"B","6":"C"}'

so on randomly till key is 100

I am trying to get all the id, keys, values of the json key where key is <= 20.

I have tried:

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

The above query ran, and should have given me 20 rows of output, instead it gave me 0. The below query ran, and gave me 20 rows but it took over 30 mins!

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

Is there a way to use a for loop or a do-while loop until x = 20 for json? Is there a way the run time be reduced?

Any help appreciated!

Upvotes: 0

Views: 399

Answers (1)

pozs
pozs

Reputation: 36214

The only operator which can query JSON keys & use indexes on jsonb (but not on json) is the ? operator. But unfortunately, you cannot use it in conjunction with <=.

However, you can use generate_series() if your queried range is relatively small:

-- use `jsonb` instead of `json`
create table foo (
  id numeric,
  x jsonb
);

-- sample data
insert into 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 an index to speed up `?` queries
create index foo_x_idx on foo using gin (x);

select distinct foo.*
from   generate_series(1, 5) s
join   foo on x ? s::text;

To work with larger ranges, you may need to extract all numeric keys of x into an integer array (int[]) & index that.

Upvotes: 1

Related Questions