I think maybe it would be more appropriate to return a blank element. It would fill an index point, but be skipped and unused in cases of iterating the loop...
http://www.2ality.com/2013/07/array-iteration-holes.html Was thinking that javascript forEach et al. methods skipped null (or undefined actually). Hmm... but then again ------------- var array = [1,2,,3]; console.log( JSON.stringify( array ) ); --------- outut : [1,2,null,3] -------------- Or more completely ------------------ var array = [1,2,,3]; console.log( JSON.stringify( array ) ); var result = JSON.parse( JSON.stringify( array ) ); array.forEach( (n)=>{ console.log(n) } ) ; result.forEach( (n)=>{ console.log(n) } ) ; --------------- output [1,2,null,3] 1 2 3 1 2 null 3 --------------------------- So seems like having null in array for JSON is perfectly expected. On Thu, Feb 4, 2016 at 11:55 PM, R Smith <rsmith at rsweb.co.za> wrote: > > > On 2016/02/05 6:34 AM, TJ O'Donnell wrote: >> >> 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. > > > I think you are mistaken in your understanding. While JSON has some rules > and some conventions, when used inside an SQL engine, the rules of SQL needs > to be adhered to before any "convention" of JSON. > Not showing Null values in JSON is a convention, not a rule. (Else, why else > would json even need the 'NULL' construct?) > > Further to this, in SQL, how would you know how many elements are present in > a json array and which of them are null if there is no way to output them? > Agreed, sometimes it isn't needed to know, but then you are welcome to > exclude them via the WHERE clause. > > You are also mistaken about the SQL convention and SQLite-specific > operations re. Nulls - If I have a table t with one single column "a" with 3 > rows (2 text values and one null value) then doing SELECT COUNT(a) FROM t; > will show 2 and SELECT COUNT(*) FROM t; will show 3, as it should - yes, > even though a is the only column. The * doesn't mean "a", even if the only > column is "a". It means "all the DB rows" and so include nulls. (The > standard might be hazy on this, I didn't check, but this is definitely how > SQLite works, and not as you suggested). > > This is also very important. Sometimes we'd want to know how many rows are > in the DB, not JUST which non-null rows are in the only column in the DB - > that is why we can decide to use either COUNT(a) or COUNT(*), or more > deliberate with an explicit GROUP BY clause. I would never want this > convention to be altered. > > >> 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'. > > > Here I'm with you - the null should output 'null' > (Devs: I'm guessing this might be an oversight in the CLI rather than the > SQL engine?) > >> 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. > > > Right you are, but first the issues need discovery - which is what is > happening in this very thread. :) > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
