Loop through JSON array in PLSQL 12.1

Question

I have JSON array inside varchar

DECLARE
 JsonArray varchar2(1000);
 arrayCars varchar2(1000);      
BEGIN
  JsonArray :={"Cars": [{"name":"Honda", "color":"red" },
                        {"name":"Toyota", "color":"green"}] }
  SELECT JSON_QUERY(JsonArray, '$.Cars') into arrayCars FROM dual;
END;
/

Now if I print out arrayCars i will get

[{"name":"Honda","color":"red"},{"name":"Toyota","color":"green"}]

But how can I loop through this Cars array and print out it's components seperately (get access to them)?

PL/SQL is not needed to extract the desired portion, using SQL is enough. — Barbaros Özhan
– Barbaros Özhan, Commented Jan 25, 2021 at 19:37

Barbaros Özhan · Accepted Answer · 2021-01-26 10:09:25Z

6

You can directly use SQL with JSON_TABLE() function which is available starting with Oracle DB 12.1.0.2 version such as

WITH t(arrayCars) AS
(
 SELECT JSON_QUERY('{"Cars": [{"name":"Honda", "color":"red" }, 
                              {"name":"Toyota", "color":"green"}] }', '$.Cars') 
   FROM dual
)
SELECT name, color
  FROM t
 CROSS JOIN JSON_TABLE(arrayCars,
                       '$' COLUMNS(NESTED PATH '$[*]'
                                    COLUMNS(
                                            name  VARCHAR2(100) PATH '$.name',
                                            color VARCHAR2(100) PATH '$.color'
                                            )
                                  )
          );

Demo

If you really need to use PL/SQL, then consider creating a function with SYS_REFCURSOR return type such as

CREATE OR REPLACE FUNCTION Get_Cars RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  JsonArray   VARCHAR2(1000);
  arrayCars   VARCHAR2(1000);
  v_sql       VARCHAR2(32767);  
BEGIN
  JsonArray :='{"Cars": [{"name":"Honda", "color":"red" },
                         {"name":"Toyota", "color":"green"}] }';
  arrayCars := JSON_QUERY(JsonArray, '$.Cars');
  DBMS_OUTPUT.PUT_LINE(arrayCars);

  v_sql := 
  'SELECT name,color
     FROM dual
    CROSS JOIN JSON_TABLE(:Cars,
                          ''$'' COLUMNS(NESTED PATH ''$[*]''
                                      COLUMNS(
                                              name  VARCHAR2(100) PATH ''$.name'',
                                              color VARCHAR2(100) PATH ''$.color''
                                              )
                                    )

            )';
  OPEN v_recordset FOR v_sql USING arrayCars;
  RETURN v_recordset;
END;
/

and then call from SQL Developer's console as

SQL> DECLARE
    result SYS_REFCURSOR;
BEGIN
   :result := Get_Cars;
END;
/

SQL> PRINT result ;

Edit(for your last comment):

Alternatively, you can use a simple implicit loop such as

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  v_name   VARCHAR2(1000);
  v_color  VARCHAR2(1000);
BEGIN
   FOR c IN (
             SELECT name,color
               FROM JSON_TABLE('{"Cars": [{"name":"Honda", "color":"red" },
                                          {"name":"Toyota", "color":"green"}] }',
                                    '$' COLUMNS(NESTED PATH '$.Cars[*]'
                                                COLUMNS(
                                                        name  VARCHAR2(100) PATH '$.name',
                                                        color VARCHAR2(100) PATH '$.color'
                                                        )
                                                )
                               )
            )
   LOOP
     v_name  := c.name;
     v_color := c.color; 
     DBMS_OUTPUT.PUT_LINE(v_name||'  '||v_color);
   END LOOP;                            
END; 
/

Demo2

edited Jan 26, 2021 at 10:09

answered Jan 25, 2021 at 19:30

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Over a year ago

Can you run your code with JSON declared into variable like in my example ? When I try this I get ORA-00900: invalid SQL statement This is critical for me. The whole JSON must be inside varchar variable

Barbaros Özhan Over a year ago

I've added the case for PL/SQL @David

David Over a year ago

I think this is a bit complicated for me, Is there any way to do a bit more easy? Without sql functions, straight inside plsql for example here you get the array arrayCars := JSON_QUERY(JsonArray, '$.Cars'); Then I need just loop through that array and use those variables for something. Lets say we have some testVar varchar and just add all variables to it inside loop. testVarchar:=testVarchar+Honda; then testVarchar:=testVarchar+red; then testVarchar:=testVarchar+Toyota; then testVarchar:=testVarchar+green;

David Over a year ago

I mean I need some loop action where I can do something and use hondas variables, then do something and use toyota variables.

Collectives™ on Stack Overflow

Loop through JSON array in PLSQL 12.1

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related