JSON_GROUP_ARRAY unexpected misuse error in UPDATE

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: JSON_GROUP_ARRAY unexpected misuse error in UPDATE

Keith Medcalf

On Friday, 10 January, 2020 14:35, Jason Dora <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> On
>Behalf Of Keith Medcalf
>Sent: Friday, 10 January, 2020 18:07
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE
>
>
>On Friday, 10 January, 2020 14:35, Jason Dora <[hidden email]> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users