Home » SQL & PL/SQL » SQL & PL/SQL » Parse Json using Oracle SQL (Oracle DB version 12.1.02)
Parse Json using Oracle SQL [message #683596] Fri, 05 February 2021 04:41 Go to next message
Shre
Messages: 4
Registered: October 2020
Junior Member
I am trying to parse JSON in Oracle SQL.

Oracle DB version 12.1.02

{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{
             "mName" : "Carol",
             "lName" : "Cena"
            },
            {
             "mName" : "Mark",
             "lName" : "Karlo"
            }
           ]
}
output should look like below:
Rownum	Name	AddressArray	mName	lname
   1	John	  Address1	 Carlo	Cena
   1	John	  Address1	 Mark	Karlo
   1	John	  Address2	 Carlo	Cena
   1	John	  Address2	 Mark	Karlo


Tried below code but not getting output like above:

select * from json_Table(
'{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{"mName" : "Carol","lName" : "Cena"},
            {"mName" : "Mark","lName" : "Karlo"}
           ]
}', 
'$' columns (  rownr number path '$.Rownum', 
               name varchar2(100) path '$.Name', 
               nested path '$.TextObj[*]' columns  (mName varchar2(100) path '$.mName',
                                                    lName varchar2(100) path '$.lName'
                                                    ),
               nested path '$.AddressArray[*]' columns(AddressArray varchar2(100) path '$')
             )
);

[Updated on: Fri, 05 February 2021 09:19]

Report message to a moderator

Re: Parse Json using Oracle SQL [message #683597 is a reply to message #683596] Fri, 05 February 2021 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do something like this:
SQL> with
  2    data as (
  3      select
  4  '{
  5   "Rownum": "1",
  6   "Name": "John",
  7   "AddressArray":["Address1", "Address2"],
  8   "TextObj":[{
  9               "mName" : "Carol",
 10               "lName" : "Cena"
 11              },
 12              {
 13               "mName" : "Mark",
 14               "lName" : "Karlo"
 15              }
 16             ]
 17  }' data from dual
 18    )
 19  select rn, name, address, mName, lName
 20  from data,
 21       json_table(data, '$'
 22         columns (
 23           rn        number       path '$.Rownum',
 24           Name      varchar2(10) path '$.Name',
 25           addresses varchar2(30) format json path '$.AddressArray',
 26           TextObj   varchar2(80) format json path '$.TextObj'
 27         )
 28       ) p1,
 29       json_table('{adresses:'||addresses||'}', '$.adresses[*]'
 30         columns (
 31           address varchar2(20) path '$'
 32         )
 33       ) p2,
 34       json_table('{names:'||TextObj||'}', '$.names[*]'
 35         columns (
 36           mName varchar2(10) path '$.mName',
 37           lName varchar2(10) path '$.lName'
 38         )
 39       ) p3
 40  /
        RN NAME       ADDRESS              MNAME      LNAME
---------- ---------- -------------------- ---------- ----------
         1 John       Address1             Carol      Cena
         1 John       Address2             Carol      Cena
         1 John       Address1             Mark       Karlo
         1 John       Address2             Mark       Karlo

4 rows selected.
Not very pretty, maybe Solomon will see this topic and provide a better solution.

Re: Parse Json using Oracle SQL [message #683610 is a reply to message #683597] Sat, 06 February 2021 06:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3046
Registered: January 2010
Location: Connecticut, USA
Senior Member
with data as (
              select '{
                       "Rownum": "1",
                       "Name": "John",
                       "AddressArray":["Address1", "Address2"],
                       "TextObj":[
                                  {
                                   "mName" : "Carol",
                                   "lName" : "Cena"
                                  },
                                  {
                                   "mName" : "Mark",
                                   "lName" : "Karlo"
                                  }
                                 ]
                      }' json_doc 
                from  dual
             )
select  ja.*,
        jt.*
  from  data,
        json_table(
                   json_doc,
                   '$'
                   columns(
                           rn   number path '$.Rownum',
                           name varchar2(10) path '$.Name',
                           nested            path '$.AddressArray[*]'
                             columns(
                                     address varchar2(10) path '$'
                                    )
                          )
                  ) ja,
        json_table(
                   json_doc,
                   '$.TextObj[*]'
                   columns(
                           mname varchar2(10) path '$.mName',
                           lname varchar2(10) path '$.lName'
                          )
                  ) jt
/

        RN NAME       ADDRESS    MNAME      LNAME
---------- ---------- ---------- ---------- ----------
         1 John       Address1   Carol      Cena
         1 John       Address1   Mark       Karlo
         1 John       Address2   Carol      Cena
         1 John       Address2   Mark       Karlo

SQL>
SY.
Re: Parse Json using Oracle SQL [message #683611 is a reply to message #683610] Sat, 06 February 2021 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Nice, I was pretty sure there was a better way to do it.

Re: Parse Json using Oracle SQL [message #683612 is a reply to message #683610] Sat, 06 February 2021 06:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3046
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oops, this will not work when JSON contains multiple "Rownum":

SQL> with data as (
  2                select '[
  3                         {
  4                          "Rownum": "1",
  5                          "Name": "John",
  6                          "AddressArray":["Address1", "Address2"],
  7                          "TextObj":[
  8                                     {
  9                                      "mName" : "Carol",
 10                                      "lName" : "Cena"
 11                                     },
 12                                     {
 13                                      "mName" : "Mark",
 14                                      "lName" : "Karlo"
 15                                     }
 16                                    ]
 17                         },
 18                         {
 19                          "Rownum": "2",
 20                          "Name": "Jim",
 21                          "AddressArray":["Address3", "Address4"],
 22                          "TextObj":[
 23                                     {
 24                                      "mName" : "Sam",
 25                                      "lName" : "Adams"
 26                                     },
 27                                     {
 28                                      "mName" : "Jose",
 29                                      "lName" : "Cuervo"
 30                                     }
 31                                    ]
 32                         }
 33                        ]' json_doc
 34                  from  dual
 35               )
 36  select  ja.*,
 37          jt.*
 38    from  data,
 39          json_table(
 40                     json_doc,
 41                     '$[*]'
 42                     columns(
 43                             rn   number path '$.Rownum',
 44                             name varchar2(10) path '$.Name',
 45                             nested            path '$.AddressArray[*]'
 46                               columns(
 47                                       address varchar2(10) path '$'
 48                                      )
 49                            )
 50                    ) ja,
 51          json_table(
 52                     json_doc,
 53                     '$.TextObj[*]'
 54                     columns(
 55                             mname varchar2(10) path '$.mName',
 56                             lname varchar2(10) path '$.lName'
 57                            )
 58                    ) jt
 59  /

        RN NAME       ADDRESS    MNAME      LNAME
---------- ---------- ---------- ---------- ----------
         1 John       Address1   Carol      Cena
         1 John       Address1   Mark       Karlo
         1 John       Address1   Sam        Adams
         1 John       Address1   Jose       Cuervo
         1 John       Address2   Carol      Cena
         1 John       Address2   Mark       Karlo
         1 John       Address2   Sam        Adams
         1 John       Address2   Jose       Cuervo
         2 Jim        Address3   Carol      Cena
         2 Jim        Address3   Mark       Karlo
         2 Jim        Address3   Sam        Adams

        RN NAME       ADDRESS    MNAME      LNAME
---------- ---------- ---------- ---------- ----------
         2 Jim        Address3   Jose       Cuervo
         2 Jim        Address4   Carol      Cena
         2 Jim        Address4   Mark       Karlo
         2 Jim        Address4   Sam        Adams
         2 Jim        Address4   Jose       Cuervo

16 rows selected.

SQL>
Corrected solution:
with data as (
              select '[
                       {
                        "Rownum": "1",
                        "Name": "John",
                        "AddressArray":["Address1", "Address2"],
                        "TextObj":[
                                   {
                                    "mName" : "Carol",
                                    "lName" : "Cena"
                                   },
                                   {
                                    "mName" : "Mark",
                                    "lName" : "Karlo"
                                   }
                                  ]
                       },
                       {
                        "Rownum": "2",
                        "Name": "Jim",
                        "AddressArray":["Address3", "Address4"],
                        "TextObj":[
                                   {
                                    "mName" : "Sam",
                                    "lName" : "Adams"
                                   },
                                   {
                                    "mName" : "Jose",
                                    "lName" : "Cuervo"
                                   }
                                  ]
                       }
                      ]' json_doc 
                from  dual
             )
select  ja.*,
        jt.mname,
        jt.lname
  from  data,
        json_table(
                   json_doc,
                   '$[*]'
                   columns(
                           rn   number path '$.Rownum',
                           name varchar2(10) path '$.Name',
                           nested            path '$.AddressArray[*]'
                             columns(
                                     address varchar2(10) path '$'
                                    )
                          )
                  ) ja,
        json_table(
                   json_doc,
                   '$[*]'
                   columns(
                           rn   number path '$.Rownum',
                           nested            path '$.TextObj[*]'
                             columns(
                                     mname varchar2(10) path '$.mName',
                                     lname varchar2(10) path '$.lName'
                                    )
                          )
                  ) jt
  where jt.rn = ja.rn
        
/

        RN NAME       ADDRESS    MNAME      LNAME
---------- ---------- ---------- ---------- ----------
         1 John       Address1   Carol      Cena
         1 John       Address1   Mark       Karlo
         1 John       Address2   Carol      Cena
         1 John       Address2   Mark       Karlo
         2 Jim        Address3   Sam        Adams
         2 Jim        Address3   Jose       Cuervo
         2 Jim        Address4   Sam        Adams
         2 Jim        Address4   Jose       Cuervo

8 rows selected.

SQL>
SY.
Re: Parse Json using Oracle SQL [message #683622 is a reply to message #683612] Mon, 08 February 2021 03:22 Go to previous message
Shre
Messages: 4
Registered: October 2020
Junior Member
Thanks Solomon and Michel. For quick reply.
Previous Topic: Regexp_like
Next Topic: Max value row and lookup value
Goto Forum:
  


Current Time: Thu Apr 15 20:40:58 CDT 2021