Re: [GENERAL] jsonb and comparison operators

[Oleg Bartunov]

jsquery (https://github.com/akorotkov/jsquery) should works for you.


On Wed, Sep 3, 2014 at 8:38 AM, Joe Van Dyk j...@tanga.com wrote:

 Is it possible to get this query (or a similar one) to use an index?

 I want to return all rows that have a value of less than 10. I have
 arbitrary keys I want to check (not just 'a').

 drop table if exists test;

 create table test (j jsonb);

 insert into test select json_build_object('a', i)::jsonb from
 generate_series(1, 10) i;
 create index on test using gin(j);

 vacuum analyze test;



 select * from test where (j-'a')::int  10;

 I tried
 select * from test where j-'a'  10::json::jsonb;
 but didn't seem to use the index.

[GENERAL] jsonb and comparison operators

[Joe Van Dyk]

Is it possible to get this query (or a similar one) to use an index?

I want to return all rows that have a value of less than 10. I have
arbitrary keys I want to check (not just 'a').

drop table if exists test;

create table test (j jsonb);

insert into test select json_build_object('a', i)::jsonb from
generate_series(1, 10) i;
create index on test using gin(j);

vacuum analyze test;



select * from test where (j-'a')::int  10;

I tried
select * from test where j-'a'  10::json::jsonb;
but didn't seem to use the index.

Re: [GENERAL] jsonb and comparison operators

[Peter Geoghegan]

On Tue, Sep 2, 2014 at 9:38 PM, Joe Van Dyk j...@tanga.com wrote:
 I want to return all rows that have a value of less than 10. I have
 arbitrary keys I want to check (not just 'a').


If you created an expression B-Tree index on 'a' it would work for
'a', but you'd have to use a jsonb literal, not a json/int4 literal.
If you want to be able to query every key at the top nesting level of
an object, such that all rows are returned with jsonbs that have
object values of which in each case one of them is, say, below 10,
then that's something that no existing opclass can support. But, why
should it be supported? That's a very fuzzy criteria to search on.

-- 
Regards,
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] jsonb and comparison operators

[Joe Van Dyk]

On Tue, Sep 2, 2014 at 9:55 PM, Peter Geoghegan peter.geoghega...@gmail.com
 wrote:

 On Tue, Sep 2, 2014 at 9:38 PM, Joe Van Dyk j...@tanga.com wrote:
  I want to return all rows that have a value of less than 10. I have
  arbitrary keys I want to check (not just 'a').


 If you created an expression B-Tree index on 'a' it would work for
 'a', but you'd have to use a jsonb literal, not a json/int4 literal.
 If you want to be able to query every key at the top nesting level of
 an object, such that all rows are returned with jsonbs that have
 object values of which in each case one of them is, say, below 10,


Just a particular key specified in the query, not just any of them.

I may want key 'a' one time, and 'b' the next time. Not sure if that's what
you meant.

I figured since I could do equality, I should be able to do less than and
greater than.

Joe



 then that's something that no existing opclass can support. But, why
 should it be supported? That's a very fuzzy criteria to search on.

 --
 Regards,
 Peter Geoghegan