SQLite Forum

json_extract behavior with escaped-double-quote keys
Login

json_extract behavior with escaped-double-quote keys

(1) By kevinlang on 2021-03-13 16:42:55 [link] [source]

Hello! I encountered the following issue when using json_extract with escape-double-quote keys.

select json_extract('{ "a": 1, "\"b\"": 2 }', '$.a');
=> 1

select json_extract('{ "a": 1, "\"b\"": 2 }', '$."a"');
=> 1

select json_extract('{ "a": 1, "\"b\"": 2 }', '$.\"b\"');
=> 2

select json_extract('{ "a": 1, "\"b\"": 2 }', '$."\"b\""');
(no result)

As you can see, json_extract supports referencing a key by both a and "a", which is to say with or without the quotes around the key.

However, it does not support both \"b\" and "\"b\"", which I would expect given the behavior for a mentioned above.

Is this a bug, or is this expected? In my case I just made sure to not wrap the keys with double quotes when building my queries. I noticed that this double-quote-wrapping support is not mentioned in the documentation, so in either case it looks like I strayed from the course a bit :).

Cheers, Kevin

(2) By Stephan Beal (stephan) on 2021-03-13 16:51:00 in reply to 1 [source]

select json_extract('{ "a": 1, ""b"": 2 }', '$.""b""');

This variation works for m:

SQLite version 3.35.0 2021-03-09 21:20:12
Enter ".help" for usage hints.
sqlite> select json_extract('{ "a": 1, "\u0022b\u0022": 2 }', '$."\u0022b\u0022"');
2

Where \u0022 ==> ".

It ain't pretty, but neither is doubling up on double quotes ;).