CODEWITHSUNDEEP
laravel
Hemant Kumar
Hemant Kumar

Reputation: 1125

Remove JSON object value Laravel from MySql database

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.

Upvotes: 1

Views: 4225

Answers (2)

Vikal Singh
Vikal Singh

Reputation: 84

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;
}

Upvotes: 1

Gary Houbre
Gary Houbre

Reputation: 880

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.

Upvotes: 0

Related Questions