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>