Oracle Apex JSON parsing with Nested Path

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!