SQLite User Forum

json_extract zero-length key string
Login

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.