I'm working with SQLite 3.13.0, and am the process of adding a new table to a
database:
CREATE TABLE settings(key TEXT PRIMARY KEY NOT NULL, value TEXT)
This table will hold arbitrarily named application defined settings. For the
value column I’d like to use JSON for every row, as some of the settings will
be structured (either as arrays or objects, possibly with nested
substructures). The json1 extension seems to cover most of what I need (without
having a separate JSON library outside of SQLite), but I've run into a problem
which might point to a missing function.
I can easily deal with arrays and objects using functions like json_array(),
but I'm having trouble with simple values, particularly strings: there appears
to be no function to turn an SQLite text value into a simple JSON text value,
without putting it in an array or object.
e.g. this is fine:
sqlite> SELECT json_array('one',2,'we"ird');
["one",2,"we\"ird"]
but I can't find a way to convert just the SQLite text 'one' into the JSON text
"one" (or 'we"ird' into "we\"ird") without the array (or object) wrapper.
A function like json_value(value) would solve the problem. It would be a single
argument function based on the implementation of json_array(), which doesn't
output the square brackets. It should also support NULL and numeric arguments,
like json_array().
Converting a simple JSON value back to native SQLite types is easy:
json_extract(json,'$'), or I can use json_each() or json_tree() to parse the
JSON values without knowing their structure.
Perhaps json_set() should be able to handle this? I tried something like this:
sqlite> SELECT json_set('null', '$', 'test');
test
It copies the string in the third parameter, but doesn't output valid JSON
because the quotes haven't been added. Is this a bug?
As an interim solution, I can modify a local copy of the json1 extension to add
my proposed function, but it would be nice if this was standard in a later
version.
Here is a draft implementation:
static void jsonValueFunc(
sqlite3_context *ctx,
int argc,
sqlite3_value **argv
){
JsonString jx;
jsonInit(&jx, ctx);
jsonAppendValue(&jx, argv[0]);
jsonResult(&jx);
sqlite3_result_subtype(ctx, JSON_SUBTYPE);
}
with this definition in the aFunc[] array:
{ "json_value", -1, 0, jsonValueFunc },
A workaround I've found is to use json_array('text') wrapped in SQLite
functions to strip the square brackets off the array, but that seems ugly.
An alternative solution would be that I only use JSON for the complex settings
and leave the simple ones stored using SQLite native types, but that either
means adding a column to track which ones are JSON, or having inherent
knowledge for each setting, which could lead to compatibility problems and
potential ambiguity if a future update changes to using JSON for an existing
setting, and an existing value happens to be valid JSON, such as the word
'true'.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
