json table loop

Solutions on MaxInterview for json table loop by the best coders in the world

showing results for - "json table loop"
Emilie
12 Mar 2020
1SQL>   declare
2  2   begin
3  3   FOR all_rec IN (select jt.*
4  4                           FROM JSON_TABLE('{
5  5       "requestId": "1e11#1660d34739a",
6  6       "result": [
7  7           {
8  8               "id": 435593,
9  9               "firstName": "Matthew",
10 10               "lastName": "Kehl",
11 11               "email": "test1@test2.com",
12 12               "updatedAt": "2017-01-03T17:01:55Z",
13 13               "createdAt": "2016-08-24T13:47:07Z"
14 14           },
15 15           {
16 16               "id": 540833,
17 17               "firstName": "Jeff",
18 18               "lastName": "Byers",
19 19               "email": "test@123.com",
20 20               "updatedAt": "2017-04-12T14:38:28Z",
21 21               "createdAt": "2016-10-31T13:15:32Z"
22 22           }
23 23       ],
24 24       "success": true
25 25   }', '$.result[*]'
26 26                                    COLUMNS (
27 27                                      "lead_id" varchar2(100) PATH '$.id',
28 28                                      "first_name" varchar2(100) PATH '$.firstName',
29 29                                      "last_name" varchar2(100) PATH '$.lastName',
30 30                                      "email_id" varchar2(100) PATH '$.email'
31 31                                  )) jt)
32 32    LOOP
33 33          dbms_output.put_line(all_rec."lead_id");          <<<=========
34 34    END LOOP;
35 35
36 36   END;
37 37  /
38435593
39540833
40
41PL/SQL procedure successfully completed.
42