json_extract zero-length key string
(1) By anonymous on 2022-04-04 12:00:41 [source]
Is there a particular reason why the following fails despite being accepted as valid JSON by SQLite, and how would one retrieve the zero-length key string if there is a way?
select json_valid('{"":"foo"}')
-- OK
select json_extract('{"":"foo"}', '$.""')
-- Error
(2.1) By midijohnny on 2022-04-04 12:56:39 edited from 2.0 in reply to 1 [link] [source]
Not quite sure I fully understand here, but maybe the following. (I switched out the literal JSON object to using JSON_OBJECT - but they should be equivalent).
SELECT
(json_each.key=="") AS is_blank_key,
json_each.value
FROM
json_each( json_object('','foo'));
Like you - I couldn't figure out any other way for the parser to accept a literal JSON path which includes that blank-key (there is no '*' operator for instance it seems).
For instance:
sqlite> select json_extract( json_object('','foo') , '$.""');
Error: JSON path error near '""'
(3) By Richard Hipp (drh) on 2022-04-04 13:25:38 in reply to 1 [link] [source]
The reason is that a zero-length JSON label is a corner case that I never considered. My first instinct is that anybody who tries to create a JSON object with a zero-length label deserves whatever it is that happens. But, I suppose I should look into it.
(4) By Larry Brasfield (larrybr) on 2022-04-04 15:48:30 in reply to 1 [link] [source]
This pickiness regarding label length in json_extract() was just fixed. If there is another patch release, it will appear there.
Thanks to Richard.
(5) By anonymous on 2022-04-05 05:07:53 in reply to 3 [link] [source]
Yet zero-length SQL identifiers work just fine.
create table "" ("" integer primary key);
(6) By Larry Brasfield (larrybr) on 2022-04-05 05:40:41 in reply to 5 [link] [source]
If you were to look at the implementation, (or even what you just wrote), you would see that the said identifiers have length 2.
(7) By ddevienne on 2022-04-05 06:30:20 in reply to 6 [link] [source]
Uh? Double-quotes have never been part of the names themselves.
They are just an artifact from the syntax. The same way single-quotes
are not part of string literal values. Enough said, no?
C:\Users\ddevienne>sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table "" ("" integer primary key);
sqlite> select length(name) from sqlite_master;
0
sqlite>
(8) By anonymous on 2022-04-05 06:38:50 in reply to 6 [link] [source]
3.38.2 disagrees with you.
sqlite> create table "" ("" integer primary key);
sqlite> .mode table
sqlite> select length(name) from sqlite_schema;
+--------------+
| length(name) |
+--------------+
| 0 |
+--------------+
sqlite>
(9) By Larry Brasfield (larrybr) on 2022-04-05 16:15:23 in reply to 8 [link] [source]
Hmmm. I was remembering what I saw in another column, dimly apparently. Yet another reason to not post well after a proper bedtime.