Removing MySQL JSON attribute if another attribute exists

Question

In my database there are many product rows with thee JSON column called 'code' and I want to:

Remove all default attributes that are next to the 'mask'-attribute,
Or if the mask exists in default area (not single default field).

{
  ...
  "access": {
    ...
  },
  "layouts": [
    {
      ...
      "grid": [
        {
          ...
          "default": "...",
          ...
        },
        {
          ...
        },
        {
          ...
        },
        {
          ...
        },
        {
          ...
          "default": "...",
          "mask": "...",
          ...
        }
      ]
    }
  ],
  ...
}

How i can do it?

The second requirements was not clear to me, but I believe using the answer below, it can be extended to whatever you need. — geertjanvdk
– geertjanvdk, Commented Feb 1, 2021 at 10:55

geertjanvdk · Accepted Answer · 2021-02-01 10:51:57Z

Here lots of folks would say: do that in some kind of script outside of MySQL. And I would agree, but where is the fun in that.

You will need, however, a Stored Routine to be able to do what you want. The issue is that JSON_REMOVE does not accept wild cards.

Based on your requirements, I came up with following routine (please test and adapt!):

DROP PROCEDURE IF EXISTS remove_defaults;
DELIMITER $$
CREATE PROCEDURE remove_defaults()
BEGIN
    DECLARE done BOOL;
    DECLARE i INT;
    DECLARE vID INT;
    DECLARE vGrid JSON;
    DECLARE vDoc JSON;

    DECLARE cur CURSOR FOR SELECT id, doc FROM t1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    SET done = FALSE;
    REPEAT
        FETCH cur INTO vID, vDoc;
        -- get the 'grid' array from the JSON document
        SET vGrid = JSON_EXTRACT(vDoc, '$.grid');
        SET i = 0;
        WHILE i < JSON_LENGTH(vGrid) DO
            -- check if this grid entry has 'mask' attribute
            IF JSON_CONTAINS_PATH(vGrid, 'one', CONCAT('$[',i,'].mask')) = 1 THEN
                -- it does have 'mask', so we remove the default
                SET vGrid = JSON_REMOVE(vGrid, CONCAT('$[',i,'].default'));
            END IF;
            SET i = i + 1;
        END WHILE;
        -- update the original JSON document with the new 'grid'
        UPDATE t1 SET doc = JSON_REPLACE(doc, '$.grid', vGrid) WHERE id = vID;
    UNTIL done END REPEAT;

    CLOSE cur;
END $$

This uses the simplified table:

CREATE TABLE t1
(
    id  INT AUTO_INCREMENT PRIMARY KEY,
    doc JSON
);

With test data like:

+----+-----------------------------------------------------------+
| id | doc                                                       |
+----+-----------------------------------------------------------+
|  1 | {"grid": [{"mask": "foo", "default": 1}, {"default": 2}]} |
|  2 | {"grid": [{"mask": "bar"}, {"default": 3}]}               |
+----+-----------------------------------------------------------+

This would result in:

+----+---------------------------------------------+
| id | doc                                         |
+----+---------------------------------------------+
|  1 | {"grid": [{"mask": "foo"}, {"default": 2}]} |
|  2 | {"grid": [{"mask": "bar"}, {"default": 3}]} |
+----+---------------------------------------------+

However if you are uncomfortable with Stored Routines, or you cannot add it to your schema, you might do this indeed outside of MySQL.

Collectives™ on Stack Overflow

Removing MySQL JSON attribute if another attribute exists

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