Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE

[Keith Medcalf]

On Friday, 10 January, 2020 14:35, Jason Dora <tonur...@gmail.com> 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