New json_quote() function added on a branch
(https://www.sqlite.org/src/info/2c3714aebf5e40e3). If there is no
pushback, and if this fixes David's problem, then this will get merged
to trunk and appear in the next release.
On 6/16/16, David Empson <demp...@emptech.co.nz> wrote:
> 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
