0

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Pran
  • 153
  • 1
  • 12
  • Does this answer your question? [Escaping ampersand character in SQL string](https://stackoverflow.com/questions/12961215/escaping-ampersand-character-in-sql-string) – Dortimer Jan 03 '20 at 18:12
  • No problem exists [in fiddle for version 18c](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3e4e46e7ec58152691b718ba847abf20) , I also tried within version 12.1.0.2. Are you sure about the sample in the question ? – Barbaros Özhan Jan 04 '20 at 20:09
  • 1
    Thanks for the help @Barbaros. No, its not working for me in oracle 12c. I have done a workaround, have replaced '&' with 'and'. – Pran Jan 09 '20 at 02:46
  • Thanks @Dortimer for the help, I have replace '&' with 'and' – Pran Jan 09 '20 at 02:47

2 Answers2

2

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.

Marco Massetti
  • 539
  • 4
  • 12
0

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

Hugh Seagraves
  • 594
  • 1
  • 8
  • 14