How to use operands for json in postgres

Question

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!

Possible duplicate of Postgres jsonb queries basic operators — Jakub Kania
– Jakub Kania, Commented Jan 27, 2016 at 11:24
From your working query I assume the sample data is {"1":"A","2":"B"}, {"3":"C","4":"A"}, etc. (What you gave is not valid JSON.) Quering for a JSON's key is not easy. The only operator which can use an index is ? for jsonb (I assume you have at least 9.4, because you used the jsonb cast). If all of my assumptions are correct, you can use generate_series(1, 20) s & then join on x ? s::text to filter for keys 1-20. — pozs
– pozs, Commented Jan 27, 2016 at 13:46
@ Jakub Kania, thank you for pointing that out. This is not a duplicate question. I did find that link you sent me before I posted my question. When I tried it, I was getting no error, but I was getting no rows as output. I mentioned that in my question. The second query works, but it takes too long to be the only feasible solution. @ pozs, I am going to try your solution and respond. You are right in your assumptions. It is a typo on my part (I am new to json, so...) — wizengeeky
– wizengeeky, Commented Jan 27, 2016 at 14:11

pozs · Accepted Answer · 2016-01-27 14:57:01Z

1

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.

answered Jan 27, 2016 at 14:57

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

7 Comments

wizengeeky Over a year ago

When I tried this, the index took a very long time to get created (and after 3 hours) I gave up. Could you tell me where I might be going wrong? I am unable to mark it as answered since it did not work for me yet.

pozs Over a year ago

Well, you can try to build it concurrently with CREATE INDEX CONCURRENTLY to avoid blocking your table. But this won't make it faster, you just don't need to hold still until it's complete. But if you have that much data in your table you should seriously consider normalizing it to something more relational-like.

wizengeeky Over a year ago

Thanks pozs, that helped

wizengeeky Over a year ago

Is there a way to ensure that from a range of the series 1-5, only those 1-5 are displayed? For instance, when I ran the query, I also noticed that the id 3 which contains 5:B also displayed 6:C though my series selection is only restricted to 5. I understand that the "?" operand is only for "select if contains", but is there a restrictive contains such as "select only" like the way an inner join works? If I am not seeking a full outer join selection, I am wondering if there is a someway to not display that.

pozs Over a year ago

@VHS there is no single operation, which will do that, but you can do a lateral join jsonb_each(x::jsonb) (just like in your 2nd query). But if your use case is really just like that (i.e. it's not a simplification of a more complex problem), you should really consider normalizing key-value pairs to another table.

|

Collectives™ on Stack Overflow

How to use operands for json in postgres

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related