Hi
I'm receiving a JSON string via RESTful services in the following format:
{
"data": {
"244376115423591": {
"first_name": "John",
"last_name": "Doe",
"review_date": "",
"startDate": "01-08-2019",
"endDate": "",
"feee": 0,
"feeeWkHours": "0",
"stretch": 0,
"payment": "300",
"recalc": "n"
},
"1234567890": {
"first_name": "Jane",
"last_name": "Doe",
"review_date": "",
"startDate": "01-09-2019",
"endDate": "",
"feee": 0,
"feeeWkHours": "0",
"stretch": 0,
"payment": "250",
"recalc": "n"
}
},
"action": "edit"
} I'm not sure, in SQL, how I can access the elements "first_name" onwards. The elements "244376115423591" and "1234567890" are completely random and could be any numeric value.
This should get you started
SQL> create table t (
2 j varchar2(4000),
3 constraint chk check ( j is json ));
Table created.
SQL>
SQL>
SQL> insert into t values ('{
2 "data": {
3 "244376115423591": {
4 "first_name": "John",
5 "last_name": "Doe",
6 "review_date": "",
7 "startDate": "01-08-2019",
8 "endDate": "",
9 "feee": 0,
10 "feeeWkHours": "0",
11 "stretch": 0,
12 "payment": "300",
13 "recalc": "n"
14 },
15 "1234567890": {
16 "first_name": "Jane",
17 "last_name": "Doe",
18 "review_date": "",
19 "startDate": "01-09-2019",
20 "endDate": "",
21 "feee": 0,
22 "feeeWkHours": "0",
23 "stretch": 0,
24 "payment": "250",
25 "recalc": "n"
26 }
27 },
28 "action": "edit"
29 } ');
1 row created.
SQL>
SQL>
SQL> select d.*
2 from t,
3 json_table(
4 t.j,
5 '$.data.*'
6 columns (
7 first_name varchar2(30 char) path '$.first_name',
8 last_name varchar2(30 char) path '$.last_name'
9 )
10 ) d;
FIRST_NAME LAST_NAME
-------------------- --------------------
John Doe
Jane Doe