I can't argue for the correctness of including nulls in aggregate functions or not. It truly is an arbitrary decision meant for standards-makers. Yet, most aggregate function do not include nulls. Interestingly, some SQL's do include them in count() but sqlite does not. In my example table, select count(a) from x returns 3, but select count(*) from x returns 4 even though a is the only column. I haven't tried every sqlite agg, but I think they all exclude null, except json_group_array and json_group_object.
In my case, I'd sure like to NOT have nulls included, but I understand needing nulls in JSON. I'm having a hard time excluding null with a where clause, as you suggested, since my actual query is the results of an intentional left join that results in rows from one table that are null. If I do a normal join or exclude nulls with a where clause, I don't get all the rows from the other table that I require. As a side issue here, but important still I think, what should json(null) mean? In my table x, select json(a) from x returns valid json integers for non-null rows, but return a sql null (a blank from command-llne sqlite) not a json null (which would be the string null) when a is null. In other words, json(null) returns null, not 'null'. I know the json stuff is new in sqlite, but I think it's worth getting these issues worked out, considering how useful json has become. TJ O'Donnell On Thu, Feb 4, 2016 at 4:54 PM, Richard Hipp <drh at sqlite.org> wrote: > On 2/4/16, TJ O'Donnell <tjo at acm.org> wrote: > > I was expecting the json_group_array aggregate function to leave out > null, > > behaving like other aggregate functions. Is this to be expected? > > > > sqlite> create table x (a integer); > > sqlite> insert into x values (1), (2), (null),(4); > > sqlite> select group_concat(a), json_group_array(a) from x; > > group_concat(a) json_group_array(a) > > If I did that, then it would be impossible to generate a JSON array > using json_group_array() that contained NULL entries. As it is, you > can usually include or omit the NULL entries using a term in the WHERE > clause: > > select json_group_array(a) from x where x is not null; > > > -- > D. Richard Hipp > drh at sqlite.org >
