SQLite Forum

json() seems to convert valid JSON5 into invalid JSON.

json() seems to convert valid JSON5 into invalid JSON.

(1) By anonymous on 2023-11-08 22:09:03 [source]

SELECT json('''Valid JSON5 string''');       --good
=> "Valid JSON5 string"                      --good canonical JSON

SELECT json('''Valid "JSON5" string''');     --good
=> "Valid "JSON5" string"                    --not good not valid JSON/5

SELECT json('"Valid "JSON5" string"');       --bad output from above
=> NULL                                      --I do not understand this, shouldn't it error?

SELECT json_valid('"Valid "JSON5" string"'); --bad output from above
=> 0                                         --correct, it's not canonical JSON

SELECT json_error_position('"Valid "JSON5" string"'); --bad output from above
=> 1                                                  --correct, error found

However, if I pass the bad output direly into json functions, it just runs with it.

SELECT json_error_position(bad_output)
  FROM (
           SELECT json('''Valid "JSON5" string''') AS bad_output
=> 0

Examples with json_extract:

SELECT json_extract('{"string":"something \"goes\" here"}', '$.string');
=> something "goes" here
--expected this is a string NOT JSON

SELECT json_extract('{"string":"something \"goes\" here"}', '$');
=> {"string":"something \"goes\" here"}
--expected this is JSON with correctly escaped quotes

SELECT json_extract('{"string":''something "goes" here''}', '$.string');
=> something "goes" here
--expected the input is JSON5 but output still just a string not JSON

SELECT json_extract('{"string":''something "goes" here''}', '$');
=> {"string":"something "goes" here"}
-- This should be canonical JSON but it's not valid JSON

Tested with sqlite3.exe from https://www.sqlite.org/2023/sqlite-tools-win-x64-3440000.zip

(2) By Richard Hipp (drh) on 2023-11-09 02:01:07 in reply to 1 [link] [source]

Thanks for the bug report.

Please verify that the problem has been fixed on the latest check-ins. You can either do your own build from sources or visit https://sqlite.org/fiddle and try out the WASM build I put there.