How to delete/remove item from document in json field in mysql laravel

Question

I have a json column named Data in my user table in the database.

Example of content:

[
{
    "id": 10,
    "key": "mail",
    "type": "male", 
}, 
{
    "id": 5,
    "key": "name",
    "type": "female",
}, 
{
    "id": 8,
    "key": "mail",
    "type": "female",
}
]

let's assume that many row in the table may have the same content so they should be removed from all of the rows of the table too what i want to do is remove an item by key and value last thing i can come up with is this query for example i want to remove the item where id equal 10:

     UPDATE
      user
    SET
      `Data` =
 JSON_REMOVE(`Data`,JSON_SEARCH(`Data`,'all',10,NULL,'$[*].id'),10)

but this query remove the all the content of the column.

If any one could help this is much appreciated.

By the way i get on this way because i can't seem to find a way to make it using QueryBuilder in laravel So it will be RawQuery.

Thank you Guys

Chargui Taieb · Accepted Answer · 2018-08-05T02:16:44.217

After a lot of manual reading and interrogation i found the answer i will post for further help for anyone in need

 UPDATE
  user
SET
  `Data` = JSON_REMOVE(
    `Data`,
    REPLACE(
    REPLACE
      (
        JSON_SEARCH(
          Data,
          'all',
          '10',
          NULL,
          '$**.id'
        ),
        '.id',
        ''
      ),
        '"',
        ''
  )
  )

==> Some explanation as i search and update the query and the content itself many times

I notice that JSON_SEARCH work only on string value if the value is int it will not find it so i cast the id(s) values of id to string after that JSON_SEARCH will return something like this $[the searched key].id but since i need to get the key of the hole item i need to remode ".id" so replace within was for that purpose and last to remove the quote from the result because it will be like this for example "$[0]" but JSON_REMOVE want it to be like this $[0] so that's the purpose of the second replace finally the item it self will be removed and the data will be updated

Hope laravel team can support those things in the future because i searched for a long hours but unfortunately no much help but we can get through with raw statement.

==> BE AWARE THAT IF THE ITEM YOU SEARCH FOR DOESN'T EXIST IN THE JSON CONTENT ALL THE JSON CONTENT WILL BE SET TO NULL

In regards to the last comment, if put into a transaction, catching Throwable and then rolling back will prevent this truncating from happening. — Pete_1, Apr 12 '19 at 05:06

score 0 · Answer 2 · answered Aug 04 '18 at 04:19

0

This is the Laravel way:

$jsonString = '[{
        "id": 10,
        "key": "mail",
        "type": "male"
    },
    {
        "id": 5,
        "key": "name",
        "type": "female"
    },
    {
        "id": 8,
        "key": "mail",
        "type": "female"
    }
]';

// decode json string to array
$data = json_decode($jsonString);

// remove item that id = 10
$data = array_filter($data, function ($item) {
    return $item->id != 10;
});

// run the query
foreach ($data as $item){
    DB::table('user')->where('id', $item->id)->update($item);
}

answered Aug 04 '18 at 04:19

Jared Chu

2,757
4
27
38

Thank you, i could make it like this in php. But i want it to be made in MySQL side with because it could be made with JSON REMOVE once i get the key of the requested item so that's why there's JSON_SEARCH but my mistake is how to know the dynamic key of the item. Anyway thank you for you effort – Chargui Taieb Aug 04 '18 at 04:28
I see, so this question is about MySQL only, not Laravel. – Jared Chu Aug 04 '18 at 06:45
The answer you post almost has fit for native php more than laravel if it's possible to get it done with the query builder in a single statement this will be good otherwise raw sql will do the trick thankalmek you though – Chargui Taieb Aug 04 '18 at 12:28

How to delete/remove item from document in json field in mysql laravel

2 Answers2