Parse JSON in PL/SQL

parse-json-in-plsql.md

Parse JSON in PL/SQL

Download and install PL/JSON.

Following is an example of how to parse a JSON array represented as string:

declare
	my_clob clob := '[
					{"element_code" : 1, "element_type":11, "element_value" : "John Doe"},
					{"element_code" : 2, "element_type":12, "element_value" : "10W384SSD"},
					{"element_code" :21, "element_type":17, "element_value" : "2013"}
					]';
	arr_    json_list;
	obj_    json;
	val_    json_value;
	el_code number(2);
	el_type number(2);
	el_val  varchar2(2000);
begin
	-- parse json array string
	arr_ := json_list(my_clob);
	for i in 1 .. arr_.count loop
		obj_    := json(arr_.get(i)); -- get i element as json object
		val_    := obj_.get('element_code');
		el_code := val_.get_number; -- read value as number
		val_    := obj_.get('element_type');
		el_type := val_.get_number; -- read value as number
		val_    := obj_.get('element_value');
		el_val  := val_.get_string; -- read value as string
		-- output read values
		dbms_output.put_line('Code: ' || el_code || ' Type: ' || el_type || ' Value: ' || el_val);
	end loop;
end;