How to check if a jsonb field contain a property name or not

General
1

Hello,
want to confirm if != null not work but =null work for jsonb field

I tried

select * from table where id=‘xxx’ and timestamp < ‘1996-01-30’ IF fields->>‘property’=null or fields->>‘property’=‘3’ limit 1;

It looks work, does it mean the property not exist in the jsonb fields.

However

when I make a query by using != null, it raise error:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Incomparable Datatypes. Cannot compare values of these datatypes

select * from table where id=‘xxx’ and timestamp < ‘1996-01-30’ IF fields->>‘property’!=null limit 1;

2

Hi @Fulton_Fu,

What type is in property ?

Can you paste an example insert row query ?

3

Sure @dorian_yugabyte, here’s steps to reproduce

  1. Create table:
CREATE TABLE testjsonb (
    identifier text,
    timestamp timestamp,
    fields jsonb,
    PRIMARY KEY ((identifier), timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);
  1. Insert data
insert into testjsonb(identifier,timestamp,fields)values('1','2020-07-01','{"property":"value1"}'); 
insert into testjsonb(identifier,timestamp,fields)values('1','2020-07-02','{"property":"value2"}'); 
insert into testjsonb(identifier,timestamp,fields)values('2','2020-07-02','{"property":"value3"}'); 
insert into testjsonb(identifier,timestamp,fields)values('3','2020-07-02','{"property":"value3"}'); 
insert into testjsonb(identifier,timestamp,fields)values('1','2020-07-02','{"property3":"value3"}');
insert into testjsonb(identifier,timestamp,fields)values('1','2020-07-03','{"property":"value3"}');
  1. Make a query
cqlsh:test> select * from testjsonb where identifier='3' and timestamp<'2020-07-03' if fields->>'property'='value3' or fields->>'property'=null;

 identifier | timestamp                       | fields
------------+---------------------------------+-----------------------
          3 | 2020-07-02 00:00:00.000000+0000 | {"property":"value3"}

Failed when

cqlsh:test> select * from testjsonb where identifier='3' and timestamp<'2020-07-03' if fields->>'property'!=null;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Incomparable Datatypes. Cannot compare values of these datatypes
select * from testjsonb where identifier='3' and timestamp<'2020-07-03' if fields->>'property'!=null;
                                                                            ^^^^^^^^^^^^^^^^^^^^
 (ql error -209)"

cqlsh:test>  select * from testjsonb where identifier='3' and timestamp<'2020-07-03' if fields->>'property4'!=null;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Incomparable Datatypes. Cannot compare values of these datatypes
select * from testjsonb where identifier='3' and timestamp<'2020-07-03' if fields->>'property4'!=null;
                                                                            ^^^^^^^^^^^^^^^^^^^^^
 (ql error -209)"
4

BTW I used Where and IF same time, it means YB will first get data by where condition then filter the results by IF condition, right?

5

Hi @Fulton_Fu

There are 2 ways you can currently fix this:

  1. Use IF NOT fields->>'property' = null
  2. Use IF fields->>'property' NOT IN ( null );

You can check here:

The WHERE clause will efficiently find the partition and correct ordering on CLUSTERING KEY. And the IF clause will scan the rows until a LIMIT is reached or until the whole partition is scanned.