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.