CODEWITHSUNDEEP
jsonoracle-databaseoracle12c
Pran
Pran

Reputation: 153

oracle - ampersand in json path

I have json data in which there is ampersand in the json path. how do I esacpe ampersand. I have tried these things but nothing is working - 1) set define off 2) escape ampersand - \& 3) using double ampersand - &&

SELECT id,array1
FROM   (
  select '{
    "data": [
      {
        "id": 2,
        "array1 & tr": "TEST",
      }
    ]
  }' AS JSON_DATA
  FROM DUAL
) I,
json_table(
  i.JSON_DATA ,
  '$.data[*]'  
  COLUMNS (
    array1 varchar2(4000) FORMAT JSON path'$."array1 & tr"',
    ID     varchar2(4000) path '$."id"'
  )  
) a

ORA-40442: JSON path expression syntax error

Upvotes: 0

Views: 711

Answers (2)

Hugh Seagraves
Hugh Seagraves

Reputation: 594

I ended up doing this; note the use of the unicode \u0026 instead of chr(38): ,"FOO_AND_BAR" path '$."FOO \u0026 BAR"'

Upvotes: 0

Marco Massetti
Marco Massetti

Reputation: 837

From the Oracle docs:

Put the "&" between braces {}

(https://docs.oracle.com/cd/B10501_01/text.920/a96518/cqspcl.htm)

From another answer:

Use ASCII equivalent of ampersand to be sure it's interpreted as string

 "array1 " || chr(38) || " tr": "TEST",

Escaping ampersand character in SQL string

I am not able to test it at the moment, but i found those answers matching your case.

Upvotes: 2

Related Questions