SQLite Forum

Document or specify what json_extract does with nonexistant keys?

Document or specify what json_extract does with nonexistant keys?

(1) By EternityForest on 2020-10-29 03:06:28 [link] [source]

Hello All! Right now, json_extract says:

4.4. The json_extract() function
The json_extract(X,P1,P2,...) extracts and returns one or more values from the well-formed JSON at X. 

If only a single path P1 is provided, then the SQL datatype of the result is NULL for a JSON null, INTEGER or REAL for a JSON numeric value, an INTEGER zero for a JSON false value, an INTEGER one for a JSON true value, the dequoted text for a JSON string value, and a text representation for JSON object and array values. 

If there are multiple path arguments (P1, P2, and so forth) then this routine returns SQLite text which is a well-formed JSON array holding the various values.

But if I try to access a nonexistant key, I get null, not an error. This seems to be what other DBs do, and is very convenient in my particular case, but nonetheless it is still undefined behavior.

Is the current behavior "How things should be"? If so, should the documentation change to reflect that? It's a bit of a "hidden feature" now.

Thanks all!

(2) By Gunter Hick (gunter_hick) on 2020-10-29 09:45:03 in reply to 1 [source]

Does your attempt match the following example from the json extension documentation?

"json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL"

(3) By EternityForest on 2020-11-12 08:05:09 in reply to 2 [link] [source]

Ah, I missed that line in the docs! Thank you for pointing that out!

Should I assume, that since it is documented as such, I can continue relying on it to return NULL instead of an error, as a way to test for existence?