Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

json index on array elements.

Is it possible to index each element inside an json array ?
something mentioned in this https://stackoverflow.com/questions/35562717/how-do-you-index-an-array-inside-a-json-with-an-oracle-12c-query
what index should i need to create in the below table - such that queries of the type ' select * from v where x2 = ??' will be index friendly ?

demo@XEPDB1> create table t(x varchar2(4000) constraint t_chk check( x is json) );

Table created.

demo@XEPDB1>
demo@XEPDB1>  insert into t(x) values('
  2     [
  3         {
  4             "foo": "100083087",
  5             "bar": [
  6                 {
  7                     "let": "DIV",
  8                     "letID": "100083088"
  9                 }
 10             ]
 11         },
 12         {
 13             "foo": "100032830",
 14             "bar": [
 15                 {
 16                     "let": "DIV",
 17                     "letID": "1000832333"
 18                 },
 19                 {
 20                     "let": "TET",
 21                     "letID": "2234832333"
 22                 }
 23             ]
 24         }
 25     ]');

1 row created.

demo@XEPDB1>    commit;

Commit complete.

demo@XEPDB1> create or replace view v
  2  as
  3  select jt.*
  4  from t, json_table( x, '$' columns
  5     ( foo varchar2(20) path '$.foo' error on error ,
  6       nested path '$.bar[*]'
  7       columns(
  8             x1 varchar2(20) path '$.let' error on error ,
  9             x2 varchar2(20) path '$.letID' error on error )) ) jt
 10  /

View created.

demo@XEPDB1> select * From v;

FOO                  X1                   X2
-------------------- -------------------- --------------------
100083087            DIV                  100083088
100032830            DIV                  1000832333
100032830            TET                  2234832333

demo@XEPDB1>
Comments
Post Details
Added on Dec 10 2020
1 comment
1,243 views