json_extract behavior with escaped-double-quote keys
(1) By kevinlang on 2021-03-13 16:42:55 [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 [link] [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 ;).
(3) By Michael P. Dubner (pyhedgehog) on 2023-08-01 00:53:14 in reply to 2 [link] [source]
Found this suggestion, and it's beautiful, but it works only if you have changed json source too:
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> .nullvalue NULL
sqlite> select json_extract('{ "a": 1, "\u0022b.\u0022": 2 }', '$."\u0022b.\u0022"');
2
sqlite> select json_extract('{ "\"b.\"": 1 }', '$."\u0022b.\u0022"');
NULL
Right now i've found only option:
sqlite> select (select value from json_tree('{ "a": 1, "\"b.": 2 }') j where fullkey='$."\"b."');
2
But more awful from my point of view is that JSON functions doesn't parses escapes at all:
sqlite> select json('{"\u0062":1,"b":2,"b":3}');
{"\u0062":1,"b":2,"b":3}
sqlite> select json('{"\u0062":1,"b":2,"b":3}')->'b';
2
sqlite> select json('{"\u0062":1,"b":2,"b":3}')->'\u0062';
1
sqlite> select json('{"b":1}')->'\u0062';
NULL
sqlite> select json('{"\u0062":1}')->'b';
NULL
sqlite> select json_remove('{"\u0062":1,"b":2,"b":3}','$.b','$.\u0062','$.b');
{"b":3}
sqlite> select json_remove(json_remove('{"\u0062":1,"b":2,"b":3}','$.b','$.\u0062'),'$.b');
{}
However in Postgres:
psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1), server 14.6)
Type "help" for help.
postgres=# select jsonb('{"\u0062":1,"b":2,"b":3}');
jsonb
----------
{"b": 3}
(1 row)
postgres=# select json('{"\"\u0062.":1}')->'"b.';
?column?
----------
1
(1 row)
postgres=# select json('{"b":1,"b":2,"\u0062":3}');
json
--------------------------
{"b":1,"b":2,"\u0062":3}
(1 row)
postgres=# select json('{"b":1,"b":2,"\u0062":3}')->'b';
?column?
----------
3
(1 row)
saltcorn=# select json('{"\u0062":1,"b":2,"b":3}')->'b';
?column?
----------
3
(1 row)