Hello,
My requirement is to perform various actions once I fetch the 'id' JSON element from the results JSON. Below JSON is generated from a third-party vendor for which I don't have a control to change the structure. The issue is, for some reason, Oracle is complaining that elements I choose from JSON 'result' array node need to be declared which I want to avoid as it would mean creating a lot of types for JSON with nested structures.
declare
begin
FOR all_rec IN (select jt.*
FROM JSON_TABLE('{
"requestId": "1e11#1660d34739a",
"result": [
{
"id": 435593,
"firstName": "Matthew",
"lastName": "Kehl",
"email": "test1@test2.com",
"updatedAt": "2017-01-03T17:01:55Z",
"createdAt": "2016-08-24T13:47:07Z"
},
{
"id": 540833,
"firstName": "Jeff",
"lastName": "Byers",
"email": "test@123.com",
"updatedAt": "2017-04-12T14:38:28Z",
"createdAt": "2016-10-31T13:15:32Z"
}
],
"success": true
}', '$.result[*]'
COLUMNS (
"lead_id" varchar2(100) PATH '$.id',
"first_name" varchar2(100) PATH '$.firstName',
"last_name" varchar2(100) PATH '$.lastName',
"email_id" varchar2(100) PATH '$.email'
)) jt)
LOOP
dbms_output.put_line(all_rec.lead_id);
END LOOP;
END;
Version:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Any help is appreciated.
Thanks,
Phani Vemi
No, it's just a simple typo. Your JSON columns are 'lead_id' not 'LEAD_ID', so some quote are needed.
SQL> declare
2 begin
3 FOR all_rec IN (select jt.*
4 FROM JSON_TABLE('{
5 "requestId": "1e11#1660d34739a",
6 "result": [
7 {
8 "id": 435593,
9 "firstName": "Matthew",
10 "lastName": "Kehl",
11 "email": "test1@test2.com",
12 "updatedAt": "2017-01-03T17:01:55Z",
13 "createdAt": "2016-08-24T13:47:07Z"
14 },
15 {
16 "id": 540833,
17 "firstName": "Jeff",
18 "lastName": "Byers",
19 "email": "test@123.com",
20 "updatedAt": "2017-04-12T14:38:28Z",
21 "createdAt": "2016-10-31T13:15:32Z"
22 }
23 ],
24 "success": true
25 }', '$.result[*]'
26 COLUMNS (
27 "lead_id" varchar2(100) PATH '$.id',
28 "first_name" varchar2(100) PATH '$.firstName',
29 "last_name" varchar2(100) PATH '$.lastName',
30 "email_id" varchar2(100) PATH '$.email'
31 )) jt)
32 LOOP
33 dbms_output.put_line(all_rec."lead_id"); <<<=========
34 END LOOP;
35
36 END;
37 /
435593
540833
PL/SQL procedure successfully completed.