Search code examples
jsonclob

JSON value from a clob field

Select JSON value from a CLOB field

The header_info field

{

"Message-ID":"<SJ0P105MB0345C789105MB0345.MAMP999.PROD.LOOKIT.COM>"

}

I tried

select JSON_VALUE('{header_info}', '$.Message-ID') from epres.mydata

I need to select the "Message-ID" I get an error ORA-40597: JSON path expression syntax error ('$.Message-ID') JZN-00209: Unexpected characters after end of path at position 10 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.

using Oracle Version 19.2.1.247

Solution

  • Use:

    '$."Message-ID"'

    It does not like the embeded - , so you need to wrap it in double-quotes.