Re: [sqlite] Ideas for optional json fields?

[Eric Grange]

Nice solution and works like a charm, thanks Clemens!

On Mon, Sep 26, 2016 at 4:22 PM, Clemens Ladisch  wrote:

> 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?

[Clemens Ladisch]

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?

[Eric Grange]

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  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

Re: [sqlite] Ideas for optional json fields?

[R Smith]

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