SQLite Forum

Proposed JSON enhancements.
Login
RE: use of json_nextract:

I might be missing something, but it's not clear to me that case/iif can effectively take the place of the nextract/ntype or a nullonerror flag argument.

Consider the following where we want to know if any given JSON has some target structure (based on the JSON1 doc example):

   select iif (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') =  '{"a":2,"c":[4,5,{"f":7}]}' , 'contains', 'does not contain') as 'answer';

This renders 'contains' or 'does not contain' so long as the JSON is well formed.

The complications start when the json is not well formed (the first "a" is missing a quote:

   select iif (json_extract('{"a:2,"c":[4,5,{"f":7}]}', '$') =  '{"a":2,"c":[4,5,{"f":7}]}' , 'contains', 'does not contain') as 'answer';

This renders a statement error.  I think there may be a use-case where we want an easy way to fold that error into the 'does not contain' category.  Here we are not concerned about why the JSON does not contain the target, simply whether it does or does not.

If we have a flag on extract/type (or a n-prefix function) that would certainly handle it, but I'm not sure it's as easy with CASE unless we wrap a JSON check first, as in:

   select iif(json_check(@j), iif (json_extract(@j, '$') =  '{"a":2,"c":[4,5,{"f":7}]}' , 'contains', 'does not contain'), 'does not contain') as 'answer');

The above is doable, but having a flag or n-prefix function to nullonerror would simplify that a bit.

RE: nullonerror with -> and -->:

If it turns out we do want to sometimes fold in malformed json into a nullonerror, consider the -> and --> as well.  --> returns various types (including null) so must cause an error on malformed json, while -> can only return JSON text, and therefore could also return null (aka nullonerror) on malformed json.