json_tree documentation improvement: change type "string" to "text"
(1) By anonymous on 2021-03-25 17:52:22 [source]
On https://www.sqlite.org/json1.html the documentation for json_tree says:
The schema for the table returned by json_each() and json_tree() is as follows:
CREATE TABLE json_tree( key ANY, -- key for current element relative to its parent value ANY, -- value for the current element type TEXT, -- 'object','array','string','integer', etc. ^******^ atom ANY, -- value for primitive types, null for array & object id INTEGER, -- integer ID for this element parent INTEGER, -- integer ID for the parent of this element fullkey TEXT, -- full path describing the current element path TEXT, -- path to the container of the current row json JSON HIDDEN, -- 1st input parameter: the raw JSON root TEXT HIDDEN -- 2nd input parameter: the PATH at which to start );
However, the type returned for JSON strings is actually not 'string', it's 'text'. The documentation would be improved if this was reworded.
/danr
(2.1) By Larry Brasfield (larrybr) on 2021-03-25 20:54:41 edited from 2.0 in reply to 1 [link] [source]
I don't mean this as a hard contradiction, more an invitation to rethink.
That SQL comment is referring to JSON types, using JSON terminology. Just as SQLite calls its character sequence type something other than "string", it calls its object and array types something other than "object" and "array". (It calls them nothing or NULL because it has no direct equivalents.)
The alleged doc deficiency goes away if the "type" field refers to the JSON type rather than what SQLite type the "atom" field may (or may not) have.
(Added via edit:) Upon reconsideration (per post 4) I agree that it would be an improvement to eliminate the mention of "string" or clarify that that is the JSON type.
(3) By Harald Hanche-Olsen (hanche) on 2021-03-25 20:05:38 in reply to 2.0 [link] [source]
But there seems to be a contradiction between the docs and the implementation, then:
▶▶▶ select * from json_tree('"foo"');
key value type atom id parent fullkey path
--- ----- ---- ---- -- ------ ------- ----
foo text foo 0 $ $
Shouldn't the type field here adhere to JSON terminology, and hence contain the string 'string' in this case?
But changing it might break many applications, so for the sake of backward compatibility, I guess we're stuck with 'text'. And then the question is once more whether the documentation ought to reflect this.
(4) By Larry Brasfield (larrybr) on 2021-03-25 20:45:17 in reply to 3 [link] [source]
Looking at this more closely, I agree with the OP that there is a slight self-contradiction in the doc page. The "type" column is said in the pseudo-definition of the table (returned by the json_tree() table-valued function) to possibly be "string" but later text says it can be "text", in agreement with the implementation. Hence, the SQL comment can lead to needless confusion at worst and useless pondering at best (unless entirely ignored.)
I see no real difficulty with 'text' being returned as that can be directly used in a CAST ( <expr> AS type ) expression with the least potential for puzzlement.
(5.1) By TripeHound on 2021-03-25 22:36:53 edited from 5.0 in reply to 2.1 [link] [source]
For objects and arrays it does return the "JSON types": "object" and "array":
select * from json_tree('{"a1":[1],"a2":"foo"}');
key value type atom id parent fullkey path
---------- --------------------- ---------- ---------- ---------- ---------- ---------- ----------
{"a1":[1],"a2":"foo"} object 0 $ $
a1 [1] array 2 0 $.a1 $
0 1 integer 1 3 2 $.a1[0] $.a1
a2 foo text foo 5 0 $.a2 $
so it's only "text" that it gets wrong. That probably cannot be changed for compatibility, but the docs should probably mention it. Perhaps:
type TEXT, -- 'object','array','text' (for a string),'integer', etc.