Remove JSON object value Laravel from MySql database

Question

I want to remove JSON object value from mysql database via eloquent. I have tried with this code it works but I have to pass array key "$.language[1]".

Here is JSON object {"name":"The Lord of the Rings:The Fellowship of the Ring","language":["Hindi","English","Spanish"]} stored in database.

Here I want to remove English language from all the records.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Book;
use DB;

class BookController extends Controller
{

    public function store(){

        $book = new Book;       
        $book->attributes = $jsonAttr;
        $book->save();
        echo "Book saved";
        die;
    }

    public function updateLanguage()
    {           
         $result = Book::where('attributes->language','LIKE','%English%')->update(['attributes' => DB::raw('JSON_REMOVE(attributes, "$.language[1]")')]);

        //$result = Book::where('attributes->language','LIKE','%H%')->get();         
        echo "<pre>";
        print_r($result);
        die;
    }
}

Any help would be appreciated.

Vikal Singh · Accepted Answer · 2019-05-08 06:49:38Z

1

Where condition fetch all the match record from Database. You need to loop this query to remove particular book language. Try this code...

public function updateLanguage()
{   
    //Get all matched records from database
    $result = Book::where('attributes->language','LIKE','%')->get();
    //Loop items to get unique id
    foreach($result as $key => $val){           
            $id = $val['id'];
            $attr = json_decode($val->attributes, true);
            $data = $attr['language'];  

            foreach($data as $itemkey => $lang){
                if($lang == "English"){                         
                    //Pass unique id to remove language from book record
                    $result = Book::where('id',$id)->update(['attributes' => DB::raw('JSON_REMOVE(attributes, "$.language['.$itemkey.']")')]);          
                }
            }
    }

    $result = Book::where('attributes->language','LIKE','%')->get();     
    foreach ($result as $key => $value) {
        print_r($value['attributes']);  
        echo "<br>";
    }

    die;
}

answered May 8, 2019 at 6:49

Vikal Singh

841 gold badge2 silver badges12 bronze badges

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

1 Comment

Hemant Kumar Over a year ago

Thanks I have got to know about where condition in laravel. with this where condition you have fetched particular book record and then update it's language.

Gary Houbre · Accepted Answer · 2019-05-07 12:39:44Z

0

You need json_decode for transform json to array : link

You remove "English" in array with unset : link

After that you can json_encode for transform array to json.

answered May 7, 2019 at 12:39

Gary Houbre

8808 silver badges27 bronze badges

1 Comment

Hemant Kumar Over a year ago

I want update json object in mysql database through laravel, the idea you suggest is not right.

Collectives™ on Stack Overflow

Remove JSON object value Laravel from MySql database

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related