Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

PLSQL Function looping thru COMPLEX JSON Object

This answer is for the simple JSON type.
PLSQL looping through JSON object

I have a complex JSON type how do i loop thru a data like this. Is there any oracle function which traverses this complex type?

[[{"PONumber":1,"ItemNumber":1,"Part":{"Description":"Tora! Tora! Tora!","UnitPrice":19.95,"UPCCode":24543013174},"Quantity":2.0},{"ItemNumber":2,"Part":{"Description":"The Beastmaster","UnitPrice":19.95,"UPCCode":13131201598},"Quantity":4.0},{"ItemNumber":3,"Part":{"Description":"Heavy Traffic","UnitPrice":19.95,"UPCCode":27616852854},"Quantity":6.0}]]

I was able to use the following function but it doesn’t give a JSON string as output.

SELECT po.PONumber,
       JSON_QUERY(jt.data, '$.LineItems.ItemNumber' WITH WRAPPER) AS Line_itemnr,
       JSON_QUERY(jt.data, '$.LineItems.Part.Description' WITH WRAPPER) AS Line_itemdesc
FROM   json_documents;
PONumber LINE_ITEMNR Line_itemdesc
[1] [1,2,3] ["Tora! Tora! Tora!","The Beastmaster","Heavy Traffic"]

I want an answer like this

PONumber LINE_ITEMNR Description……. Rest of the columns here
1 1 Tora! Tora! Tora!
1 2 The Beastmaster
1 3 Heavy Traffic

Thanks in advance for your response.

Tags:

2

Answers


  1. 0

    Your question isn’t really all that clear about what you’re trying to get out. You’re not even very clear about whether you’re searching for a SQL solution, or a PL/SQL solution.

    However – going by the heading of your post (which mentions PL/SQL), I’m going to take a punt at the following solution. You could easily wrap this in a function and modify it to your needs.

    DECLARE
   json_  VARCHAR2(2000) := '[
      [{
          "PONumber":1,
          "ItemNumber":1,
          "Part": {"Description": "Tora! Tora! Tora!", "UnitPrice": 19.95, "UPCCode": 24543013174},
          "Quantity": 2.0
       }, {
          "ItemNumber": 2,
          "Part": {"Description": "The Beastmaster", "UnitPrice": 19.95, "UPCCode": 13131201598},
           "Quantity":4.0
       }, {
          "ItemNumber": 3,
          "Part": {"Description": "Heavy Traffic", "UnitPrice": 19.95, "UPCCode": 27616852854},
          "Quantity":6.0
       }]]';
   jobj1_  json_object_t;
   jobj2_  json_object_t;
   kys1_   json_key_list;
   kys2_   json_key_list;
   jarr1_  json_array_t := json_array_t(json_);
   jarr2_  json_array_t;
   el_     json_element_t;
   val_    VARCHAR2(2000);
   ky1_    VARCHAR2(200);
   ky2_    VARCHAR2(200);
BEGIN
   FOR a1_ IN 0 .. jarr1_.get_size - 1 LOOP
      Dbms_Output.Put_Line ('  >> Loop on array 1');
      jarr2_ := json_array_t (jarr1_.get(a1_));
      FOR a2_ IN 0 .. jarr2_.get_size - 1 LOOP
         Dbms_Output.Put_Line ('    >> Loop on array 2 => object');
         jobj1_ := json_object_t (jarr2_.get(a2_));
         kys1_  := jobj1_.get_keys;
         FOR k_ IN 1 .. kys1_.count LOOP
            ky1_ := kys1_(k_);
            el_  := jobj1_.get(kys1_(k_));
            IF el_.is_number THEN
               val_ := el_.stringify;
            ELSIF el_.is_object THEN
               jobj2_ := json_object_t(el_);
               kys2_  := jobj2_.get_keys;
               Dbms_Output.Put_Line ('      >> Object Level 2');
               FOR k2_ IN 1 .. kys2_.count LOOP
                  ky2_ := kys2_(k2_);
                  el_  := jobj2_.get(kys2_(k2_));
                  Dbms_Output.Put_Line ('      ' || ky2_ || ': ' || el_.stringify);
               END LOOP;
            END IF;
            Dbms_Output.Put_Line ('    ' || ky1_ || ': ' || val_);
         END LOOP;
      END LOOP;
   END LOOP;
END;

    — edit —
    question appears to have been updates since I posted this.

    Login or Signup to reply.
  2. 0

    You can use JSON_TABLE in a query:

    SELECT jt.*
FROM   jsondocuments j
       CROSS JOIN JSON_TABLE(
         j.data,
         '$[*][*]'
         COLUMNS (
           ponumber    NUMBER        PATH '$.PONumber',
           itemnumber  NUMBER        PATH '$.ItemNumber',
           description VARCHAR2(200) PATH '$.Part.Description',
           unitprice   VARCHAR2(200) PATH '$.Part.UnitPrice',
           upccode     VARCHAR2(200) PATH '$.Part.UPCCode',
           quantity    NUMBER        PATH '$.Quantity'
         )
       ) jt

    Which, for the sample data:

    CREATE TABLE jsondocuments (data CLOB CHECK (data iS JSON));

INSERT INTO jsondocuments (data)
VALUES ('[[{"PONumber":1,"ItemNumber":1,"Part":{"Description":"Tora! Tora! Tora!","UnitPrice":19.95,"UPCCode":24543013174},"Quantity":2.0},{"ItemNumber":2,"Part":{"Description":"The Beastmaster","UnitPrice":19.95,"UPCCode":13131201598},"Quantity":4.0},{"ItemNumber":3,"Part":{"Description":"Heavy Traffic","UnitPrice":19.95,"UPCCode":27616852854},"Quantity":6.0}]]')

    Outputs:

    PONUMBER ITEMNUMBER DESCRIPTION UNITPRICE UPCCODE QUANTITY
    1 1 Tora! Tora! Tora! 19.95 24543013174 2
    null 2 The Beastmaster 19.95 13131201598 4
    null 3 Heavy Traffic 19.95 27616852854 6

    Or:

    SELECT jt.*
FROM   jsondocuments j
       CROSS JOIN JSON_TABLE(
         j.data,
         '$[*]'
         COLUMNS (
           ponumber    NUMBER        PATH '$[0].PONumber',
           NESTED PATH '$[*]' COLUMNS (
             itemnumber  NUMBER        PATH '$.ItemNumber',
             description VARCHAR2(200) PATH '$.Part.Description',
             unitprice   VARCHAR2(200) PATH '$.Part.UnitPrice',
             upccode     VARCHAR2(200) PATH '$.Part.UPCCode',
             quantity    NUMBER        PATH '$.Quantity'
           )
         )
       ) jt

    Which outputs:

    PONUMBER ITEMNUMBER DESCRIPTION UNITPRICE UPCCODE QUANTITY
    1 1 Tora! Tora! Tora! 19.95 24543013174 2
    1 2 The Beastmaster 19.95 13131201598 4
    1 3 Heavy Traffic 19.95 27616852854 6

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search