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
