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

0 0

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 .

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

lucas.jellema@amis.nl

https://www.amis.nl/

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Juggling with SQL Types in SQL and PL/SQL – fading borders between Collection and Table OOW Presentation: Optimal Use of Oracle Database 10g and Oracle Database 11g for Modern Application Development Oracle Database Cross Session Data Cache – Introducing the Globally Accessible Database Context Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API SOA & SOA Suite for Oracle Database Professionals – seminars in Perth and Melbourne and Singapore (November 2009)