Parse JSON Array in SQL and PL/SQL – turn to a Nested Table

Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON.

I recently was working on a Node.js application that exposed a REST API to HTTP consumers. The consumers could send POST requests with a body that could hold various complex request parameters. The Node.js application used the Oracle DB Driver for Node to connect to the database and invoke PL/SQL units to retrieve data from which the HTTP Response would be constructed. One of the input parameters to the PL/SQL procedure was a string that could contain a JSON array. This allowed transfer of potentially many parameter values.

A JSON array is a string constructed like this:

 ["mit", "nach", "nebst", "bei"] 

To PL/SQL, this is just a string with a single value. My challenge was to turn this single value into the multiple values that were intended.

Oracle Database 12c -12.1.0.2 – introduced support for JSON. Part of this support is the operator JSON_TABLE that can be used in a SQL query to turn [parts of] a JSON document into relational data. The query that does the trick for a simple JSON array with Scalar values looks like this:

SELECT value
FROM json_table('["content", "duration"]', '$[*]'
COLUMNS (value PATH '$'
)
)

Or more general:

with json as
( select '["mit", "nach", "nebst", "bei"]' doc
from dual
)
SELECT value
FROM json_table( (select doc from json) , '$[*]'
COLUMNS (value PATH '$'
)
)

I used this query in a simple PL/SQL function, that is invoked with a VARCHAR2 holding a JSON array and returns a table of VARCHAR2 with the individual values in the JSON array:

create or replace
FUNCTION json_array_to_string_tbl (
p_json_array IN VARCHAR2
) RETURN string_tbl_t
is
l_string_tbl string_tbl_t:= string_tbl_t();
begin
if p_json_array is not null and length(p_json_array)>0
then
SELECT value
bulk collect into l_string_tbl
FROM json_table( p_json_array, '$[*]'
COLUMNS (value PATH '$'
)
);
end if;
return l_string_tbl;
end json_array_to_string_tbl;

The definition of the STRING_TBL_T is quite simply:

create or replace
type string_tbl_t as table of varchar2(2000);

This function can used for example like this:

select column_value
from table(json_array_to_string_tbl('["mit", "nach", "nebst", "bei"]'))

In cases where the JSON array does not hold scalar values but instead JSON objects, such as:

[{"firstName": "Tobias", "lastName":"Jellema"},{"firstName": "Anna", "lastName":"Vink"} ]

A similar approach can be used.

And the SQL query could read:

with json as  
( select '[{"firstName": "Tobias", "lastName":"Jellema"},{"firstName": "Anna", "lastName":"Vink"} ]' doc  
  from   dual  
)  
SELECT first_name 
,      last_name 
FROM  json_table( (select doc from json) , '$[*]'  
                COLUMNS ( first_name PATH '$.firstName'  
                        , last_name PATH '$.lastName'  
                        )  
               )

In this case, an ADT – Abstract Data Type – (aka UDT) could be defined to bring some structure from JSON to SQL and PL/SQL as well:

create type person as object 
( first_name varchar2(50) 
, last_name  varchar2(50) 
);

And the SQL query could read:

with json as   
( select '[{"firstName": "Tobias", "lastName":"Jellema"},{"firstName": "Anna", "lastName":"Vink"} ]' doc   
  from   dual   
)   
SELECT person( first_name , last_name) person  
FROM  json_table( (select doc from json) , '$[*]'   
                COLUMNS ( first_name PATH '$.firstName'   
                        , last_name PATH '$.lastName'   
                        )   
               )

This shows how easy it is to transfer multi-value JSON based data structures to PL/SQL as simple string values and have them interpreted in PL/SQL.

Try out the statements shown in this article on Oracle LiveSQL .