# How to check if a jsonb field contain a property name or not **URL:** \ **Category:** General\ **Created:** [June 30, 2020, 9:13am UTC](https://forum.yugabyte.com/t/how-to-check-if-a-jsonb-field-contain-a-property-name-or-not/746 "2020-06-30T09:13:46Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [June 30, 2020, 9:13am UTC](https://forum.yugabyte.com/t/how-to-check-if-a-jsonb-field-contain-a-property-name-or-not/746/1 "2020-06-30T09:13:46Z")
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; ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [June 30, 2020, 2:20pm UTC](https://forum.yugabyte.com/t/how-to-check-if-a-jsonb-field-contain-a-property-name-or-not/746/2 "2020-06-30T14:20:07Z")
Hi @Fulton_Fu, What type is in `property` ? Can you paste an example insert row query ? ---
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [July 1, 2020, 1:11am UTC](https://forum.yugabyte.com/t/how-to-check-if-a-jsonb-field-contain-a-property-name-or-not/746/3 "2020-07-01T01:11:38Z")
Sure @dorian_yugabyte, here’s steps to reproduce 1. Create table: ```auto CREATE TABLE testjsonb ( identifier text, timestamp timestamp, fields jsonb, PRIMARY KEY ((identifier), timestamp) ) WITH CLUSTERING ORDER BY (timestamp DESC); ``` 1. Insert data ```auto 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 ```auto 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 ```auto 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)" ``` ```auto 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)" ``` ---
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [July 1, 2020, 1:15am UTC](https://forum.yugabyte.com/t/how-to-check-if-a-jsonb-field-contain-a-property-name-or-not/746/4 "2020-07-01T01:15:35Z")
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? ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [July 2, 2020, 9:42am UTC](https://forum.yugabyte.com/t/how-to-check-if-a-jsonb-field-contain-a-property-name-or-not/746/5 "2020-07-02T09:42:20Z")
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 );` > [@Fulton\_Fu](#): > > 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? You can check here: > **[SELECT statement \[YCQL\]](https://docs.yugabyte.com/preview/api/ycql/dml_select/)** > > Use the SELECT statement to retrieve (part of) rows of specified columns that meet a given condition from a table. 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.