Re: [sqlite] Ideas for optional json fields?
Nice solution and works like a charm, thanks Clemens! On Mon, Sep 26, 2016 at 4:22 PM, Clemens Ladischwrote: > Eric Grange wrote: > > I am generating a json with json_object() function, and I would like to > > omit the null or default fields from the output json > > SELECT (SELECT json_group_object(key, value) > FROM json_each(json_object('field1', field1, 'field2', field2)) > WHERE value IS NOT NULL) > FROM MyTable; > > > Regards, > Clemens > ___ > 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
Re: [sqlite] Ideas for optional json fields?
Eric Grange wrote: > I am generating a json with json_object() function, and I would like to > omit the null or default fields from the output json SELECT (SELECT json_group_object(key, value) FROM json_each(json_object('field1', field1, 'field2', field2)) WHERE value IS NOT NULL) FROM MyTable; Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ideas for optional json fields?
Using coalesce on the value produces the field with the JSON, with an empty string (or whatever it was coalesced again). What I really would like to get is {"field1":"value"} rather than {"field1":"value","field2":null} or {"field1":"value","field2":""} I also tried setting the field name to null, but that triggers an error "json_object() labels must be TEXT" On Mon, Sep 26, 2016 at 2:28 PM, R Smithwrote: > > On 2016/09/26 11:15 AM, Eric Grange wrote: > >> Hi, >> >> I am generating a json with json_object() function, and I would like to >> omit the null or default fields from the output json (for compacity >> reasons) >> >> When there is only one such field, I have used a "case when" like in >> >> >> select >> case when field2 is null then >>json_object('field1', field1) >> else >>json_object('field1', field1, 'field2', field2) >> end >> ... >> >> >> but when there are multiple such optional fields, this approach becomes >> unwieldy. >> > > I'm not experienced playing with the JSON extension much, but the solution > that feels correct to me would be: > > select json_object('field1', COALESCE(field1,'""'), 'field2', > COALESCE(field2,'""')) > > or some such variant that produces the correct output... > ___ > 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
Re: [sqlite] Ideas for optional json fields?
On 2016/09/26 11:15 AM, Eric Grange wrote: Hi, I am generating a json with json_object() function, and I would like to omit the null or default fields from the output json (for compacity reasons) When there is only one such field, I have used a "case when" like in select case when field2 is null then json_object('field1', field1) else json_object('field1', field1, 'field2', field2) end ... but when there are multiple such optional fields, this approach becomes unwieldy. I'm not experienced playing with the JSON extension much, but the solution that feels correct to me would be: select json_object('field1', COALESCE(field1,'""'), 'field2', COALESCE(field2,'""')) or some such variant that produces the correct output... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users