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 Smith <rsm...@rsweb.co.za> wrote:
>
> 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
