6 Replies Latest reply on Nov 28, 2019 7:47 AM by cormaco

    How to Parse JSON

    devlarry

      I am using Oracle Database 12c, APEX 18.2, and ORDS 3. I have a rest call to our Oracle Identity Manager. I am recieving back JSON and trying to pull the values. I have tried several different solutions and cannot get any to work on part of the JSON. Below is the latest that works great until I try to add ["urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User"] to get the MailCode.

      The JSON is ["urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User"] and I tried several ways to get the value.

      I used an online JSON formatter to get the path listing:

         obj.Resources[0].schemas[0] = ["urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User"]

      I tried using $.Resources[0].schemas[0].MailCode and I do not get an error but the MailCode is coming up blank.

      I have googled for a way to get the [urn:] values but cannot find anything. Does anyone know how I can get this value.

       

      Last     First            Email                            Mail

      Koss    Lawrence    Koss.Larry@epa.gov

       

      with t as

      ( SELECT jt.data

          FROM JSON_DATA_TABLE jt )

        select jt.* from t,

        json_table(data, '$[*]'

        columns (last  varchar2(128) path '$.Resources[0].name.familyName',

                 first varchar2(128) path '$.Resources[0].name.givenName',

                 email varchar2(20)  path '$.Resources[0].emails[0].value',

                 mail  varchar2(128) path '$.Resources[0].urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User.MailCode')) jt;

       

      ORA-40597: JSON path expression syntax error ('$.Resources[0].urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User.MailCode')

      JZN-00209: Unexpected characters after end of path

      at position 19

      40597. 00000 -  "JSON path expression syntax error ('%s')%s\nat position %s"

      *Cause:    The specified JavaScript Object Notation (JSON) path expression

                 had invalid syntax and could not be parsed.

      *Action:   Specify JSON path expression with the correct syntax.

        • 1. Re: How to Parse JSON
          cormaco

          Please post a short example JSON-file.

          • 2. Re: How to Parse JSON
            devlarry

            Cormaco, here is a shortened version of the JSON.

            {

              "schemas": [

                "urn:ietf:params:scim:api:messages:2.0:ListResponse"

              ],

              "totalResults": 1,

              "Resources": [

                {

                  "urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User": {

                    "organizations": [],

                    "Upn": "Koss.Larry@epa.gov",

                    "PSBCity": "DALLAS",

                    "Department": "R06-ECAD",

                    "PreferredFirstName": "Larry",

                    "Company": "R06",

                    "City": "Dallas",

                    "RoomNumber": "6131",

                    "MailCode": "ECD"

                  },

                  "name": {

                    "familyName": "Koss",

                    "givenName": "Lawrence",

                    "middleName": "E"

                  },

                  "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User": {

                    "manager": {

                      "displayName": "Osbourne, Margaret"

                    },

                    "department": "VC000000"

                  },

                  "userName": "LKOSS",

                  "addresses": [

                    {

                      "type": "work",

                      "formatted": "1201 Elm Street",

                      "postalCode": "75270",

                      "region": "TX"

                    }

                  ],

                  "emails": [

                    {

                      "type": "work",

                      "value": "Koss.Larry@epa.gov"

                    }

                  ],

                  "phoneNumbers": [

                    {

                      "type": "mobile",

                      "value": "469-504-2588"

                    },

                    {

                      "type": "work",

                      "value": "214-665-6533"

                    }

                  ],

                  "displayName": "Koss, Larry"

                }

              ]

            }

             

            I was able to figure out how to get the "urn:ietf:...." values. The issue I have now is getting the multiple phone numbers. I know its y code as this is the first time I've been workng on a JSON pull. I tried apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[%d].value',p0 => i,p1 => i) and that will work on a single record return or first record of multiple. I tired using the zero (0) position but all I get are blanks

            apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[0].value',p0 => i) I even tried apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[%d].value',p0 => i,p1 => 0) using %d or %s as the second identifier. Any thoughts or guidance is appreciated.

             

            FOR i IN 1 .. l_count

              LOOP

                INSERT INTO EIDW_PEOPLE

                (USERNAME,FAMILYNAME,GIVENNAME,PREFERREDFIRSTNAME,EMAIL,ADDRESS,CITY,

                ADD_STATE,POSTALCODE,ROOMNUMBER,DISPLAYNAME,TPHONE1,NPHONE1,

                TPHONE2,NPHONE2,COMPANY,MAILCODE,DEPARTMENT,PPDEPT,SUPERVISOR)

                VALUES (apex_json.get_varchar2(p_path => 'Resources[%d].userName',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].name.familyName',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].name.givenName',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".PreferredFirstName',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].emails[%d].value',p0 => i,p1 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].addresses[%d].formatted',p0 => i,p1 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".City',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].addresses[%d].region',p0 => i,p1 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].addresses[%d].postalCode',p0 => i,p1 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".RoomNumber',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].displayName',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[%d].type',p0 => i,p1 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[%d].value',p0 => i,p1 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[%d].type',p0 => i,p1 => i+1),

                apex_json.get_varchar2(p_path => 'Resources[%d].phoneNumbers[%d].value',p0 => i,p1 => i+1),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".Company',p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".MailCode', p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".Department', p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:enterprise:2.0:User".department', p0 => i),

                apex_json.get_varchar2(p_path => 'Resources[%d]."urn:ietf:params:scim:schemas:extension:enterprise:2.0:User".manager.displayName', p0 => i)

              );

              END LOOP;

            • 3. Re: How to Parse JSON
              cormaco

              If you want the phone numbers in separate columns as in your code use this:

              select jt.* from t,
                json_table(data, '$[*]'
                columns (last    varchar2(20) path '$.Resources[0].name.familyName',
                         first   varchar2(20) path '$.Resources[0].name.givenName',
                         email   varchar2(20)  path '$.Resources[0].emails[0].value',
                         mail    varchar2(20) path '$.Resources[0]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".MailCode',
                         tphone1 varchar2(10)  path '$.Resources[0].phoneNumbers[0].type',
                         nphone1 varchar2(20) path '$.Resources[0].phoneNumbers[0].value',
                         tphone2 varchar2(10)  path '$.Resources[0].phoneNumbers[1].type',
                         nphone2 varchar2(20) path '$.Resources[0].phoneNumbers[1].value'
                )) jt;
              
              
              
              
              LAST                 FIRST                EMAIL                MAIL                 TPHONE1    NPHONE1              TPHONE2    NPHONE2             
              -------------------- -------------------- -------------------- -------------------- ---------- -------------------- ---------- --------------------
              Koss                 Lawrence             Koss.Larry@epa.gov   ECD                  mobile     469-504-2588         work       214-665-6533        
              
              
              

               

              This works of course only for a predetermined number of phoneNumbers, to get all phone numbers in separate rows use this:

              select jt.* from t,
                json_table(data, '$[*]'
                columns (last    varchar2(20) path '$.Resources[0].name.familyName',
                         first   varchar2(20) path '$.Resources[0].name.givenName',
                         email   varchar2(20) path '$.Resources[0].emails[0].value',
                         mail    varchar2(20) path '$.Resources[0]."urn:ietf:params:scim:schemas:extension:oracle:2.0:OIG:User".MailCode',
                         nested path '$.Resources[0].phoneNumbers[*]'
                         columns (
                            tphone varchar2(10) path '$.type',
                            nphone varchar2(20) path '$.value'
                         )          
                )) jt;
              
              LAST                 FIRST                EMAIL                MAIL                 TPHONE     NPHONE              
              -------------------- -------------------- -------------------- -------------------- ---------- --------------------
              Koss                 Lawrence             Koss.Larry@epa.gov   ECD                  mobile     469-504-2588        
              Koss                 Lawrence             Koss.Larry@epa.gov   ECD                  work       214-665-6533        
              
              
              
              
              • 4. Re: How to Parse JSON
                devlarry

                Thanks it works but I'm only getting one record on other json data. I know the sample I sent had one row but I have several that have hundreds of users. Each one has a separate id and I am trying to pull one and process all the records.

                WITH t AS (SELECT JSON_DATA data FROM EIDW_JSON_TABLE WHERE EIDW_ID = 'R06')

                select jt.* from t, 

                  json_table(data, '$[*]' 

                  columns (last    varchar2(20) path '$.Resources[0].name.familyName',  .....

                This gives just one row of data.

                 

                The data has around 794 users. I have to pull about 20 separate divisions and put them in a table. What I plan is to have a report that the user chooses the division and then the script will pull that data into an interactive report. This is what I am currently doing with tables but we are trying to move to REST services instead.

                • 5. Re: How to Parse JSON
                  devlarry

                  Cormaco,

                   

                  Solved it. Everything is working. Thanks for your help. You got me on the right track. Unfortunately the actions don't list as solved.

                   

                  Thank you again for your assistance.

                  Lawrence

                  1 person found this helpful
                  • 6. Re: How to Parse JSON
                    cormaco

                    Unfortunately the actions don't list as solved.

                    Check this FAQ:

                    How can I mark answers as Correct or Helpful?