Thanks Richard. Tested and it works for me. I tried it with text, integer and null types as input.
It also removes one step in storing JSON-encoded integer values, since I no longer need to convert them to text. > On 18/06/2016, at 1:04 AM, Richard Hipp <d...@sqlite.org> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
