Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE

[Keith Medcalf]

And, of course, if you want your JSON array to be in MRU order, you can use 
this:

update users as O
   set urls = (
   select json_group_array(distinct value)
 from (
   select ?2 as value
  union all
   select value
 from json_each(urls)
 join users
where userid = O.userid
  )
  )
 where userid == ?1
   and ?2 is not null;

or

update users as O
   set urls = (
   select json_group_array(value)
 from (
   select ?2 as value
  union all
   select value
 from json_each(urls)
 join users
where userid = O.userid
  and value != ?2
  )
  )
 where userid == ?1
   and ?2 is not null;

or for LRU order this:

update users as O
   set urls = (
   select json_group_array(value)
 from (
   select value
 from json_each(urls)
 join users
where userid = O.userid
  and value != ?2
  union all
   select ?2 as value
  )
  )
 where userid == ?1
   and ?2 is not null;

The advantage of course is that you can specify a collation such as nocase for 
the "value != ?2 collate nocase" so that HttP://WwW.GooGle.Com is the same as 
http://www.google.com without having to normalcase all your URLs first ...

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Friday, 10 January, 2020 18:07
>To: SQLite mailing list 
>Subject: Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE
>
>
>On Friday, 10 January, 2020 14:35, Jason Dora  wrote:
>
>>I have a workflow where I would like to push an item onto a JSON array,
>>while ensuring the items on the array are unique. And I'm able to write
>a
>>working statement in a SELECT, but the same logic fails in a UPDATE.
>
>You need to define what you mean by "push an item onto a JSON array".  Do
>you want the array to be ordered by insertion order or merely contain
>sorted distinct entries?
>
>>Assume there is a table named "users" with the columns UserId and URLs.
>>UserId being the primary key and all URLs values being well formatted
>>JSON
>>e.g. [], ["http://google.com;], etc.
>
>>Assume then I want to add "http://foobar.com; to the URLs for UserId 1.
>
>>This SELECT will return the expected value:
>>SELECT JSON_GROUP_ARRAY((SELECT value FROM
>>JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
>>http://foobar.com')) GROUP BY value)) AS URLs FROM users WHERE UserId=1;
>
>This does not appear to actually do what you want ... at least not when I
>execute it with test data ...
>
>>Trying to UPDATE using the same pattern generates a "misuse of aggregate
>>function" error:
>>UPDATE users SET URLs=JSON_GROUP_ARRAY((SELECT value FROM
>>JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
>>http://foobar.com')) GROUP BY value)) WHERE UserId=1;
>
>That is a correct error message.
>
>JSON_GROUP_ARRAY is an aggregate function applicable to select ... it is
>not a scalar function.  It works exactly the same way all the time.
>
>The following query appends a value to the end of the array if it is not
>already in the array ...
>
>select json_group_array(distinct value)
>  from (
>select value
>  from (
>select value
>  from json_each(urls)
>  join users
> where userid=?
>)
>   union all
>values (?)
>   );
>
>and the corresponding update statement to add an arbitrary value bound as
>parameter 2 to the userid bound as parameter 1 would be:
>
>update users as O
>   set urls = (
>   select json_group_array(distinct value)
> from (
>   select value
> from (
>   select value
> from json_each(urls)
> join users
>where userid = O.userid
>   )
>  union all
>   values (?2)
>  )
>  )
> w

Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE

[Keith Medcalf]

On Friday, 10 January, 2020 14:35, Jason Dora  wrote:

>I have a workflow where I would like to push an item onto a JSON array,
>while ensuring the items on the array are unique. And I'm able to write a
>working statement in a SELECT, but the same logic fails in a UPDATE.

You need to define what you mean by "push an item onto a JSON array".  Do you 
want the array to be ordered by insertion order or merely contain sorted 
distinct entries?

>Assume there is a table named "users" with the columns UserId and URLs.
>UserId being the primary key and all URLs values being well formatted
>JSON
>e.g. [], ["http://google.com;], etc.

>Assume then I want to add "http://foobar.com; to the URLs for UserId 1.

>This SELECT will return the expected value:
>SELECT JSON_GROUP_ARRAY((SELECT value FROM
>JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
>http://foobar.com')) GROUP BY value)) AS URLs FROM users WHERE UserId=1;

This does not appear to actually do what you want ... at least not when I 
execute it with test data ...

>Trying to UPDATE using the same pattern generates a "misuse of aggregate
>function" error:
>UPDATE users SET URLs=JSON_GROUP_ARRAY((SELECT value FROM
>JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
>http://foobar.com')) GROUP BY value)) WHERE UserId=1;

That is a correct error message.

JSON_GROUP_ARRAY is an aggregate function applicable to select ... it is not a 
scalar function.  It works exactly the same way all the time.

The following query appends a value to the end of the array if it is not 
already in the array ...

select json_group_array(distinct value)
  from (
select value
  from (
select value
  from json_each(urls)
  join users
 where userid=?
)
   union all
values (?)
   );

and the corresponding update statement to add an arbitrary value bound as 
parameter 2 to the userid bound as parameter 1 would be:

update users as O
   set urls = (
   select json_group_array(distinct value)
 from (
   select value
 from (
   select value
 from json_each(urls)
 join users
where userid = O.userid
   )
  union all
   values (?2)
  )
  )
 where userid == ?1
   and ?2 is not null;

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE

[Jason Dora]

Hello SQLite masters,

I have a workflow where I would like to push an item onto a JSON array,
while ensuring the items on the array are unique. And I'm able to write a
working statement in a SELECT, but the same logic fails in a UPDATE.

Assume there is a table named "users" with the columns UserId and URLs.
UserId being the primary key and all URLs values being well formatted JSON
e.g. [], ["http://google.com;], etc.

Assume then I want to add "http://foobar.com; to the URLs for UserId 1.

This SELECT will return the expected value:
SELECT JSON_GROUP_ARRAY((SELECT value FROM
JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
http://foobar.com')) GROUP BY value)) AS URLs FROM users WHERE UserId=1;

Trying to UPDATE using the same pattern generates a "misuse of aggregate
function" error:
UPDATE users SET URLs=JSON_GROUP_ARRAY((SELECT value FROM
JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
http://foobar.com')) GROUP BY value)) WHERE UserId=1;

However, an additional INNER SELECT in the UPDATE will result in the
expected behavior:
UPDATE users SET URLs=(SELECT JSON_GROUP_ARRAY(value) FROM (SELECT value
FROM JSON_EACH(JSON_SET(URLs,'$['||JSON_ARRAY_LENGTH(URLs)||']','
http://foobar.com')) GROUP BY value)) WHERE UserId=1;

Since my expectation is for the JSON_GROUP_ARRAY function to behave the
same for SELECTs and UPDATEs. I think the above may be a SQLITE error and
am reporting it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] json_group_array( json_object())

[ingo]

On 5-7-2019 20:14, Richard Hipp wrote:
> Can you please send a complete example?

While preparing that the problem was resolved. A search showed that a
wrong concatenation || further down caused  this result.

Something I noticed before when working with json is that my errors in
the code result in strange results and not in an error.


Thanks Richard,

Ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] json_group_array( json_object())

[Richard Hipp]

On 7/5/19, ingo  wrote:
> The following:
>
> json_object (
>   'data', json_group_array(
>  json_object(
>'type', type,
>'id', notebook_id,
>'attributes', json_object(
>   'book', book,
>   'total_notes', total_notes
>), ...etc
>
> results in:
>
> {"data":"[{\"type\":\"notebook\",\"id\":2,\"attributes\":{\"book\":\"brew\",\...

Unable to repro the problem.

Can you please send a complete example?  Also send the version number
of SQLite that you are running.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] json_group_array( json_object())

[ingo]

The following:

json_object (
  'data', json_group_array(
 json_object(
   'type', type,
   'id', notebook_id,
   'attributes', json_object(
  'book', book,
  'total_notes', total_notes
   ), ...etc

results in:

{"data":"[{\"type\":\"notebook\",\"id\":2,\"attributes\":{\"book\":\"brew\",\...
etc

the array is a string?

besides manually constructing with:
 || json_quotes('data')
 || ":["
 || group_concat(
json_object( )
)

how should it be done properly in one go?

TIA,

ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] json_group_array() and sorting

[Eric Grange]

Hi,

Is json_group_array() supposed to honor a sorting clause or not ? (and
concatenation aggregates in general)

I have a query like

select json_group_array(json_object(
   'id', st.id,
   'num', st.numeric_field,
   ...bunch of fields here...
))
from some_table st
...bunch of joins here...
where ...bunch of conditions...
order by st.numeric_field desc
limit 50


but the resulting JSON array is not ordered according to the "order by",
but AFAICT by the st.id field (a primary key)
When not aggregating, the records are in the correct order.

Is it a bug or something expected ?

I can get the proper order when I use a subquery for the joins & filters,
and aggregate in a top level query, but that is
rather more verbose, and I am not sure the ordering being preserved in that
case is not just 'circumstancial' and could
be affected by future SQLite query optimizations.

Thanks!

Eric
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] json_group_array

[Michael Falconer]

I just love this list and the contained discussions. Doffing my cap to
Keith and his succinct and very useful summary of the subtle differences
regarding aggregate type functions. Especially like the nested stuff, which
I must admit had not really occurred to me. Nice stuff Keith and thanks for
sharing that info. It also clarifies the underlying fact that these
functions are often slightly misunderstood even by experienced SQL hacks.


On 6 February 2016 at 07:24, Keith Medcalf  wrote:

>
> count(*) counts the rows of the result set selected
> count(column) counts the NOT NULL values in the column of the result set
> selected
> count(DISTINCT column) counts the number of distinct values (excluding
> NULLs) in the column of the result set selected
>
> count(column IS NULL) is equivalent to count(*) (the expression always
> returns true (1) or false(0)
> count(column IS NOT NULL) is equivalent to count(*)  for every row so
> therefore all rows are counted)
>
> sum(column IS NULL) returns the count of the number of rows in which the
> column is null
> sum(column IS NOT NULL) returns the count of the number of rows in which
> the column is not null
>
> These can be combined, so for example count(DISTINCT column) + sum(column
> IS NULL) returns the number of unique values in the column where NULLs are
> distinct.
> and count(DISTINCT column) + (sum(column IS NULL) > 0) returns the number
> of unique values where NULLs are not distinct.
>
> and so on and so forth
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of R Smith
> > Sent: Friday, 5 February, 2016 00:55
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] json_group_array
> >
> >
> >
> > 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

[sqlite] json_group_array

[Hick Gunter]

The row still counts. Just like counting christmas presents before you open 
them. The fact that someone gave you an empty wrapper only becomes apparrent 
when you look inside.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Yannick 
Duch?ne
Gesendet: Freitag, 05. Februar 2016 12:38
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] json_group_array

On Fri, 5 Feb 2016 07:51:06 +
Hick Gunter  wrote:

> That is because count(a) and count(*) means two different things. The first 
> counts values, the second counts rows.

What if all columns of a row, are NULL?

--
Yannick Duch?ne
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] json_group_array

[Keith Medcalf]

count(*) counts the rows of the result set selected
count(column) counts the NOT NULL values in the column of the result set 
selected
count(DISTINCT column) counts the number of distinct values (excluding NULLs) 
in the column of the result set selected

count(column IS NULL) is equivalent to count(*) (the expression always 
returns true (1) or false(0) 
count(column IS NOT NULL) is equivalent to count(*)  for every row so therefore 
all rows are counted)

sum(column IS NULL) returns the count of the number of rows in which the column 
is null
sum(column IS NOT NULL) returns the count of the number of rows in which the 
column is not null

These can be combined, so for example count(DISTINCT column) + sum(column IS 
NULL) returns the number of unique values in the column where NULLs are 
distinct.
and count(DISTINCT column) + (sum(column IS NULL) > 0) returns the number of 
unique values where NULLs are not distinct.

and so on and so forth

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of R Smith
> Sent: Friday, 5 February, 2016 00:55
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] json_group_array
> 
> 
> 
> 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

[sqlite] json_group_array

[Yannick Duchêne]

On Fri, 5 Feb 2016 07:51:06 +
Hick Gunter  wrote:

> That is because count(a) and count(*) means two different things. The first 
> counts values, the second counts rows.

What if all columns of a row, are NULL?

-- 
Yannick Duch?ne

[sqlite] json_group_array

[Stephan Beal]

On Fri, Feb 5, 2016 at 11:23 AM, J Decker  wrote:

> -
> var array = [1,2,,3];
> console.log( JSON.stringify( array ) );
> -
> outut : [1,2,null,3]
> ...
> So seems like having null in array for JSON is perfectly expected.
>

See also:

http://stackoverflow.com/questions/30585552/how-to-represent-an-array-with-empty-elements-in-json

tl;dr: "jsonlint" also says empty array elements aren't allowed.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] json_group_array

[R Smith]

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

[Hick Gunter]

That is because count(a) and count(*) means two different things. The first 
counts values, the second counts rows.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von TJ 
O'Donnell
Gesendet: Freitag, 05. Februar 2016 05:34
An: Richard Hipp
Cc: SQLite mailing list
Betreff: Re: [sqlite] json_group_array

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

> On 2/4/16, TJ O'Donnell  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); select
> > sqlite> 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
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] json_group_array

[J Decker]

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

[sqlite] json_group_array

[TJ O'Donnell]

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

> On 2/4/16, TJ O'Donnell  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
>

[sqlite] json_group_array

[Richard Hipp]

On 2/4/16, TJ O'Donnell  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

[sqlite] json_group_array

[TJ O'Donnell]

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)
---  ---
1,2,4[1,2,null,4]

TJ O'Donnell