SQL JSON query operator – Part III

This entry is about JSON_EXISTS. JSON_EXISTS takes a path expression and check if that path selects one (or multiple) values in the JSON data.


demo@ORA12C> create table t( doc_id  int,
  2     doc_details varchar2(1000),
  3     constraint valid_json_check check(doc_details is json) );

Table created.

demo@ORA12C> insert into t values (1 , '{"id":1, "name" : "Jeff"}' );

1 row created.

demo@ORA12C> insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );

1 row created.

demo@ORA12C> insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );

1 row created.

demo@ORA12C> insert into t values (4 , '{"name" : "John", "status":"Silver"}' );

1 row created.

demo@ORA12C> commit;

Commit complete.



Let’s find rows where the field name ‘status’ exists.


demo@ORA12C> select count(*)
  2  from t
  3  where json_exists(doc_details,'$.status');

  COUNT(*)
----------
         3

demo@ORA12C>


We can use JSON_EXISTS to enforce that every customer has an ID field using a check constraint, and also clean up the data having no ID fields. 


demo@ORA12C> delete from t
  2  where not json_exists(doc_details,'$.id') ;

1 row deleted.

demo@ORA12C> alter table t
  2  add constraint t_id_check
  3  check( json_exists(doc_details,'$.id') );

Table altered.

demo@ORA12C> insert into t(doc_id,doc_details)
  2     values (4 , '{"name" : "John", "status":"Silver"}' );
insert into t(doc_id,doc_details)
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.T_ID_CHECK) violated


demo@ORA12C>