We face difficulties when we parsing nested path json content, in this post we will see how to parse using nested path with json content.
before we see you need to know below names;
- JSON_TABLE
- NESTED PATH
Refer this link :- Reference Oracle Document
json content :-
{
“results”: [
{
“id”: “VVeknpp8SEgSZ2uAFYMy6F”,
“group_id”: “Ux96Ehnn8d8V5kNxEMLtSM”,
“group”: {
“id”: “Ux96Ehnn8d8V5kNxEMLtSM”,
“name”: “Member”,
“is_system”: true
},
“user”: {
“id”: “uEnQEkJwRgQAjWNoqSzKxn”,
“iid”: 10753694,
“email”: “thamaraiapex@gmail.com”,
“first_name”: “Thamarai”,
“last_name”: “Apex”,
“avatar”: “www.google.com”,
“signup_source”: “”,
“phone_number”: “1234567890”,
“is_suspended”: false
},
“workspace”: “cAJEaNFBAEBdQ6NmogNd2j”,
“is_owner”: false,
“removed”: false,
“is_active”: false,
“date_created”: “2020-08-10T12:17:51.530171Z”,
“date_updated”: “2020-08-11T12:10:40.711818Z”,
“role”: 1,
“is_support”: false,
“license”: “Full”
}
]
}
Below PL/SQL Code To Get Id from direct path and get user email from nested path
DECLARE
v_url VARCHAR2(1000);
v_clob CLOB;
v_json_content VARCHAR2(4000);
v_access_token VARCHAR2(500);
v_id VARCHAR2(500);
v_avatar VARCHAR2(4000);
v_email VARCHAR2(4000);
json_content apex_json.t_values;
ujson_content apex_json.t_values;
v_not_email_address VARCHAR2(500);
BEGIN
v_access_token := 'AB1234JKL';
IF v_access_token IS NOT NULL THEN
v_url := 'https://api.thamarai/workspaces/'|| v_workspace_id|| '/users';
apex_web_service.g_request_headers.DELETE();
apex_web_service.g_request_headers(1).name := 'Authorization';
apex_web_service.g_request_headers(1).value := 'Bearer ' || v_access_token;
SELECT
id,
email
INTO
v_id,
v_email
FROM
dual,
JSON_TABLE ( apex_web_service.make_rest_request(p_url => v_url, p_http_method => 'GET'), '$.results[*]'
COLUMNS (
id VARCHAR2 ( 200 ) PATH '$.id',
NESTED PATH '$.user[*]'
COLUMNS (
email VARCHAR2 ( 200 CHAR ) PATH '$.email'
)
)
);
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001, sqlerrm);
END;
Stored the id and user email into v_id and v_email variables.
In JSON content results is the main path and user is the nested path.
Happy Apexing!