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