SQLite Forum

JSON literals, how they work

JSON literals, how they work

(1) By Wout Mertens (wmertens) on 2020-11-02 13:20:47 [link] [source]

Hi all,

I'm wondering about why these queries return what they return. I would expect them all to return the same thing, the first result:

sqlite> select json_set('{}','$.t',json('true')); {"t":true}

sqlite> select json_set('{}','$.t','true'); {"t":"true"}

sqlite> select json_set('{}','$.t',CAST('true' as JSON)); {"t":0}

How does it work?


(2) By Gunter Hick (gunter_hick) on 2020-11-02 13:33:21 in reply to 1 [source]

There is no JSON type. Giving a declared type of JSON results in numeric affinity. The literal 'true' does not look like a number so the result of the cast is 0.

(3) By Wout Mertens (wmertens) on 2020-11-02 13:43:54 in reply to 2 [link] [source]

But how does JSON('true') then work? What does it convert to? In theory, it should be a string but when I provide 'true', the JSON for a boolean, it thinks it's '"true"'

(4) By Gunter Hick (gunter_hick) on 2020-11-02 13:59:21 in reply to 3 [link] [source]

Works as documented.

"If the value of a path/value pair is an SQLite TEXT value, then it is normally inserted as a quoted JSON string, even if the string looks like valid JSON. However, if the value is the result of another json1 function (such as json() or json_array() or json_object()) then it is interpreted as JSON and is inserted as JSON retaining all of its substructure."

'true' is a string, so json_set inserts a quoted string.

json('true') is a json object, so json_set inserts the json object without adding quotes.

(5) By ddevienne on 2020-11-02 14:20:06 in reply to 4 [link] [source]

And Wout, the API behind this is https://www.sqlite.org/c3ref/value_subtype.html

The value returned by the json() function is tagged as a JSON value,
which another function can pickup and act upon. These subtypes are not visible
or accessible when the value is returned to the statement, only the type is.

And AFAIK, there's no equivalent of typeof() for subtypes, although writing
one would be easy, yet the returned integer wouldn't tell you much, it is
basically an opaque thing meant for two (or more) cooperating functions
to pass a modicum of information between them.

(6) By Wout Mertens (wmertens) on 2020-11-02 19:31:00 in reply to 5 [link] [source]

Ok, so json() is a sort of proprietary "CAST". Now I understand thanks!