Why is my JSON_REMOVE working on MariaDB but not MySQL?

Question

It seems to me they are the same in both docs. But on MySQL it says 0 rows affected. The command does run but doesn't change anything. I have 2 databases, a local one which uses 10.4.22-MariaDB and one hosted on Heroku which uses 8.0.23 (MySQL) I am using MySQL workbench for both, not sure why MariaDB is installed anyways since I always intended to use MySQL but they're basically the same

So I have this JSON_REMOVE command, and it doesnt work on my Heroku-hosted database which uses 8.0.23 MySQL with JawsDB add-on.

My question is, how can I make this work on MySQL as well? It should work there as far as I can tell. I replace the arrow brackets with real values of course. users is the users table followings is a column of type JSON (array)

-- remove specific postID from user's json array
UPDATE users
SET followings = JSON_REMOVE(followings, replace(json_search(followings, 'one', <postID>), '"', ''))
WHERE json_search(followings, 'one', <postID>) IS NOT NULL
AND username = "<username>";

Here is a sample data of the users Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(45) UNIQUE,
    password VARCHAR(45),
    role VARCHAR(20) DEFAULT "user",
    followings JSON DEFAULT '[]' COMMENT "array of post IDs which user follows"
);

INSERT INTO users
(username, password, role, followings)
VALUES
('user123', 'mypass', 'user', '[1, 2]')

Answer 1

A solution (not a nice one) could be to define your own function like this:

CREATE DEFINER=`root`@`localhost` FUNCTION `MY_JSON_REMOVE`(j JSON, i INT) RETURNS json
    DETERMINISTIC
BEGIN
     select json_arrayagg(c1) into j from json_table(j,'$[*]' columns(c1 JSON PATH "$[0]")) j where c1 <> i;
RETURN j;
END

Doing: select my_json_remove('[1,2,3,4,5,6,7]',3);

Returns: [1, 2, 4, 5, 6, 7]

Because overriding a MySQL function seems not possible, you will have to use MY_JSON_REMOVE() (or whatever name you give it).