How to remove object by property in JSON array using MySQL functions?

Question

Given the following JSON stored inside a MySQL json data type:

"users": [
    {
        "group": "manager",
        "userID": "a123"
    },
    {
        "group": "employee",
        "userID": "a456"
    }
]

How can I remove user object with "userID": "a456" without knowing its position in the array?

If we know the position then the following works:

$query = 'UPDATE jsontable SET
jsondata = JSON_REMOVE
(
    jsondata, 
    "$.users[1]"
);';

However, what I'm looking for is something like this:

$query = 'UPDATE jsontable SET
jsondata = JSON_REMOVE
(
    jsondata, 
    (
        JSON_SEARCH
        (
            jsondata, 
            "all",
            JSON_OBJECT("userID", "a456")
        )
    )
);';

The above produces a data truncated error for the jsondata column. Other variations such as putting the userID directly instead of JSON_OBJECT also produce errors.

What do I need to fix in the query to make JSON_SEARCH return the path to the specific object that I want to remove from the array?

Bearing in mind that it should only search inside of users, as there might be other properties on the main JSON object using those IDs.

Michael · Accepted Answer · 2018-06-09 05:43:53Z

2

I think this JSON_SEARCH could work for you. It should return the path for you. The last parameter '$**.users' indicates to search only in paths called users

SELECT 
    JSON_SEARCH(jsondata, 'one', 'a456', null, '$**.users') 
    FROM jsontable

Find here a Dbfiddle JSON_SEARCH

UPDATE

Combined statement with JSON_REMOVE, JSON_SEARCH and the REPLACE function which unquotes the result of JSON_SEARCH.

UPDATE jsontable
    SET jsondata = JSON_REMOVE( 
        jsondata, REPLACE( 
            JSON_SEARCH( jsondata, 'one', 'a456', null, '$**.users' )
            , '"'
            , ''
        ) 
      );

DBFiddle with JSON_REMOVE

Additional Infos:

edited Jun 9, 2018 at 5:43

answered Jun 8, 2018 at 20:37

Michael

5662 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Optimae Over a year ago

Thanks, do you mean using this SELECT inside of the update clause? If so it'll result in an invalid JSON path expression error - I'm not sure if this is syntax related or if it's just not possible to run a SELECT JSON_SEARCH inside of a JSON_REMOVE as its path parameter.

Michael Over a year ago

@Optimae a replace should do the trick. Take a look at the update.

Optimae Over a year ago

Hey @Michael, thanks for updating, this still results in a Data truncated for column 'jsondata' at row 1 error, I'll set up a dbfiddle though to share.

Optimae Over a year ago

Thanks, although I still get data truncated errors it seems to be a PHP/PDO/localised issue and this fiddle does indeed work, so marking as correct!

Manonandan S K · Accepted Answer · 2021-10-25 15:12:37Z

0

Adding to Michael's answer.

To remove double quote he is using REPLACE function.
For this we can use JSON_UNQUOTE.

Moreover the solution will not work, because the path returned from JSON_SEARCH will be pointing to the search string passed. So it will remove the key value pair from the object instead of object.
So, to remove complete object we have to get the previous path of the json.

UPDATE jsontable
    SET jsondata = JSON_REMOVE( 
        jsondata, JSON_UNQUOTE(
            REPLACE( 
                JSON_SEARCH( jsondata, 'one', 'a456', null, '$**.users' )
                , '.userID'
                , ''
            )
        ) 
    );

As we need previous path I am Hardcoding it here.

answered Oct 25, 2021 at 15:12

Manonandan S K

6621 gold badge8 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to remove object by property in JSON array using MySQL functions?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related