0

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?

Improve this question

2 Answers 2

Reset to default
1

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

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

3 Comments

Thanks for you response. I have already implemented this JSON_TABLE. But, I really want to know how to use it with FOR LOOP. Like myarray json_array_t := [1,2,3]; for 0 to myarray.size() loop .... etc.
I don't understand really, if you have json_table already implemented , just use it in for loop. Why again json_array ?json_table gives you the ability to convert your JSON to column value as similar to table
With the first Select query in my question, I got an array of values. If I could assign that array to a variable with "select into var from table", I have my array object. Then I can use array operations, either to traverse with loop or any other operations. It may be a good option to use json_table, but I want to understand the array usage.
1

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

Improve this answer

9 Comments

Thanks for you response. I have already implemented this JSON_TABLE. But, I really want to know how to use it with FOR LOOP. Like myarray json_array_t := [1,2,3]; for 0 to myarray.size() loop .... etc. I need to pass the ids to another function one at a time in a loop.
well @SureshVeeranala , you're welcome, can check the edit out.
can we do it without using the table? I just want to store the array in a variable and do the array operation on that variable.
@Barbaros Özhan: I really appreciate the effort from your end and even though I am not the OP I took many learning points from this answer +1. Thank you.
I agree @Sujitmohanty30. The answers are very informative.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.