Unescape JSON strings with JSON_TABLE in MariaDB

321 Views Asked by At

I have trouble with the JSON_TABLE function in MariaDB 10.6.5. This is my query:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;

What I get back is the XML string, but not properly unescaped:

<root language=\"de\"></root>

When I use then JSON_VALUE instead:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  JSON_VALUE(@DATA, '$[0].Data');

then I get back the correctly unescaped string:

<root language="de"></root>

When I do the same on a MySQL 8.0.26 server, it works as expected:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;

-- correct:
-- <root language="de"></root>

SELECT
  JSON_VALUE(@data, '$[0].Data');

-- correct:
-- <root language="de"></root>

It looks to me that this difference in behaviour is a bug in MariaDB's side. Any hints on how to get that working in MariaDB?

0

There are 0 best solutions below