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?