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 ;).

(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)