CODEWITHSUNDEEP
sqljsonoracle-databaseplsqloracle19c
Suresh Veeranala
Suresh Veeranala

Reputation: 1

Processing JSON Arrays in PL/SQL

I have a JSON document stored in myjson table, doc column.

{
  "totalResultsCount": 19,
  "geonames": [
    {
      "geonameId": 2593109,
      "fcode": "ADM1"
    },
    {
      "geonameId": 3336899,
      "fcode": "ADM1"
    }, ...

When I run the query,

select j.doc.geonames.geonameId from myjson j where j.doc.geonames.fcode  like '%ADM1%';

I get an array of IDs.

[2593109,3336899,3114710,2521383,3336903,2593110,3336898,3336900,2593111,3336901,2519582,2593112,3336902,3336897,3117732,6362988,2513413,3115609,2593113]

How do I read this array and print each ID using a FOR LOOP?

Upvotes: 0

Views: 2711

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can use JSON_TABLE() function within Oracle's SQL containing a CROSS JOIN in order to parse individual column values for product and fcode, and use them in SELECT-list and WHERE condition, respectively as below :

SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_id, js.product 
  FROM myjson
 CROSS JOIN
       JSON_TABLE( 
                 doc, '$.geonames[*]' COLUMNS (
                                               product VARCHAR(100) PATH '$.geonameId',
                                               fcode   VARCHAR(100) PATH '$.fcode'
                                              )
        ) js
  WHERE js.fcode = 'ADM1'

Demo

Update : Evaluate the following depending on your comment of need for array assignment of the returning values :

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  json_array_t OWA.VC_ARR;
BEGIN
  FOR c IN
  (
   SELECT ROW_NUMBER() OVER (ORDER BY 1) - 1 AS row_id, js.product 
     FROM myjson
    CROSS JOIN JSON_TABLE( 
                          doc, '$.geonames[*]' COLUMNS (
                                               product VARCHAR(100) PATH '$.geonameId',
                                               fcode   VARCHAR(100) PATH '$.fcode'
                                               )
                ) js
    WHERE js.fcode = 'ADM1' )
  LOOP    
    json_array_t(c.row_id) := c.product;
    DBMS_OUTPUT.PUT_LINE( 'value for json_array_t('||c.row_id||') is '||json_array_t(c.row_id) );
  END LOOP;
END;
/

Demo

Update 2 : You can set array values by using your original query, and then extract all members of that array using a FOR LOOP as desired lastly :

DECLARE
  l_str          VARCHAR2(32767);
  l_top_obj      JSON_OBJECT_T;
  l_dept_arr     JSON_ARRAY_T;
  l_dept_obj     JSON_OBJECT_T;
  l_emp_arr      JSON_ARRAY_T;
  l_emp_obj      JSON_OBJECT_T;
BEGIN
  SELECT j.doc 
    INTO l_str
    FROM myjson j 
   WHERE j.doc.geonames.fcode LIKE '%ADM1%';    
  
  l_top_obj := JSON_OBJECT_T(l_str);
  l_dept_arr := l_top_obj.get_array('geonames');
  
  FOR i IN 0 .. l_dept_arr.get_size - 1 
  LOOP
    l_dept_obj := TREAT(l_dept_arr.get(i) AS JSON_OBJECT_T);
    DBMS_OUTPUT.PUT_LINE('index  : '||i||' - geonameId : ' || l_dept_obj.get_number('geonameId'));
  END LOOP;
END;
/

Demo

Upvotes: 1

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

We can use JSON_TABLE for this purpose ,

SQL/JSON function json_table projects specific JSON data to columns of various SQL data types. You use it to map parts of a JSON document into the rows and columns of a new, virtual table, which you can also think of as an inline view.

WITH json AS
 (SELECT '[2593109,3336899,3114710,2521383,3336903,2593110,3336898,3336900,2593111,3336901,2519582,2593112,3336902,3336897,3117732,6362988,2513413,3115609,2593113]' doc
  FROM   dual)
SELECT geonameId
FROM   json_table((SELECT doc FROM json)
                 ,'$[*]' columns(geonameId NUMBER path '$'))

Edit Using JSON_TABLE as source in for loop

begin
 for i in (SELECT geonameid
             FROM json_table('[2593109,3336899,3114710,2521383,3336903,2593110,3336898,3336900,2593111,3336901,2519582,2593112,3336902,3336897,3117732,6362988,2513413,3115609,2593113]'
                             ,'$[*]' columns(geonameId NUMBER path '$')))
 loop
   dbms_output.put_line('Id: '||i.geonameid);
 end loop;
end;
/

Again not sure if you want this.

Reference fiddle

Upvotes: 1

Related Questions