Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Parse JSON

devlarryNov 22 2019 — edited Nov 28 2019

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.

Comments
Post Details
Added on Nov 22 2019
6 comments
1,128 views