0

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]')
T S
  • 245
  • 2
  • 10
  • Hi, it woudl be useful if you showed us at least an example of the data this is processing – RiggsFolly Feb 28 '22 at 10:31
  • Why should I provide a [Minimal Reproducible Example for any SQL query question?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – RiggsFolly Feb 28 '22 at 10:33
  • @RiggsFolly kk edited to include data sample – T S Feb 28 '22 at 10:35
  • Your code looks working on MariaDB 10.5: https://sqlize.online/sql/mariadb/730419cf0f4ae7f9fd469201a28e10e3/ – Slava Rozhnev Feb 28 '22 at 10:38
  • 1
    That is a nightmare way of storing a list of posts a user is following. You are using a Relational database, its designed for storing the type of information using a link table. That will also make processing (adding/removing) links a completely simple process, whereas using a JSON string in a column is a bad and often Overly Complex way – RiggsFolly Feb 28 '22 at 10:39
  • @SlavaRozhnev it works on my Maria (the version is stated) , but not on my MySQL 8.0x that is my issue, i need to make it work on MySQL as well , and it only works on my Maria – T S Feb 28 '22 at 10:39
  • Did you check the MySQL docs about [JSON_REMOVE](https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-remove)? – Luuk Feb 28 '22 at 10:41
  • @RiggsFolly you're right I do regret going this route lol. But a little late for me to go back on this project since its due in a couple of days. Anyways it is working perfectly fine , just MySQL production host is not working the same as Maria for whatever reason – T S Feb 28 '22 at 10:41
  • 3
    This is looks as MySQL bug. Look this https://stackoverflow.com/questions/68882785/json-search-difference-in-mysql-and-mariadb – Slava Rozhnev Feb 28 '22 at 10:44
  • 1
    BTW: your `CREATE TABLE` does not work on MySQL, and your `INSERT` is not working on MySQL and MariaDB . – Luuk Feb 28 '22 at 10:54
  • 4 column names - 6 inputs??? Lets try some real data that matches the CREATE and also the JSON_REMOVE – RiggsFolly Feb 28 '22 at 11:02

1 Answers1

0

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).

Luuk
  • 12,245
  • 5
  • 22
  • 33