mysql json field json_remove()

Question

I have a mysql json field (interactions) that holds values something like this:

{"likes":
    [
        ['1:scott'],
        ['2:james']
    ]
}

I want to be able to remove one of the objects. Something like this (obviously this won't work, as it's a value, not a key):

update `user`
set `post` = JSON_REMOVE(interactions, '$.likes."[2:james]"')
where `id` = 3

When Done:

{"likes":
    [
        ['1:scott']
    ]
}

I've been banging my head on this all night, and have tried to come up with solutions based on a variety of posts found here and in other places, with no luck of piecing something together that gives me exactly what I need. Any help appreciated, thanks!

just trying to understand as I am using it for the first time : can you run it and tell me what does this return ? update user set post = JSON_REMOVE(interactions, '$.likes') where id = 3 and JSON_EXTRACT(interactions , '$.likes.2') = 'james' — Prabhat G
– Prabhat G, Commented Jun 16, 2017 at 6:25
Invalid JSON path expression. The error is around character position 9. — Pete_1
– Pete_1, Commented Jun 16, 2017 at 6:59

wchiquito · Accepted Answer · 2017-06-16 16:53:06Z

An option that you can use is shown in the following script: (depending on the number of rows in the table you may have performance problems, adjust as needed):

mysql> DROP TABLE IF EXISTS `user`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `user` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `post` JSON,
    ->   `interactions` JSON
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO
    ->   `user` (`post`, `interactions`)
    -> VALUES
    ->   (
    ->     '{"likes": [["1:scott"],["3:kitty"]]}',
    ->     '{"likes": [["1:scott"],["3:kitty"]]}'
    ->   ),
    ->   (
    ->     '{"likes": [["2:james"],["1:scott"]]}',
    ->     '{"likes": [["2:james"],["1:scott"]]}'
    ->   ),
    ->   (
    ->     '{"likes": [["1:scott"],["2:james"]]}',
    ->     '{"likes": [["1:scott"],["2:james"]]}'
    ->   );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SET @`search` := '2:james';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `user`
    -> SET `post` = COALESCE(JSON_REMOVE(`interactions`, LEFT(
    ->     JSON_UNQUOTE(
    ->       JSON_SEARCH(`interactions`,
    ->                   'one',
    ->                   @`search`,
    ->                   NULL,
    ->                   '$.likes')
    ->     ),
    ->     CHAR_LENGTH(
    ->       JSON_UNQUOTE(
    ->         JSON_SEARCH(`interactions`,
    ->                     'one',
    ->                     @`search`,
    ->                     NULL,
    ->                     '$.likes'))) - 3)), `interactions`)
    -> -- WHERE `id` = 3
    -> ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> SELECT 
    ->   `id`,
    ->   `post`,
    ->   `interactions`
    -> FROM
    ->   `user`;
+----+---------------------------------------+---------------------------------------+
| id | post                                  | interactions                          |
+----+---------------------------------------+---------------------------------------+
|  1 | {"likes": [["1:scott"], ["3:kitty"]]} | {"likes": [["1:scott"], ["3:kitty"]]} |
|  2 | {"likes": [["1:scott"]]}              | {"likes": [["2:james"], ["1:scott"]]} |
|  3 | {"likes": [["1:scott"]]}              | {"likes": [["1:scott"], ["2:james"]]} |
+----+---------------------------------------+---------------------------------------+
3 rows in set (0.00 sec)

See db-fiddle.

Collectives™ on Stack Overflow

mysql json field json_remove()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related