Monday, May 22, 2017

Laravel / Eloquent : Get only a subJson of a mysql json field

I have a mysql database (MySQL 5.7.1) with a single table called "inventory". This table contains 2 columns :

  • parentId {String}
  • dataInv {json}

So, each entry in my table contains a json data defined as follow:

{
    testId : "4",
    lang : "en",
    subTask : {
        inv : {
           hasright : true,
           isNew : false
        }
    }
}

In my Inventory model class, i defined :

 protected $casts = [
     'dataInv' => 'array',
 ];

In my InventoryController, i defined a method to get the "inv" structure for a given parentId:

public function getInventoryByParentId($parentId) {
    $inventory = Inventory::where('parentId', $parentId)->first();
    return response()->json($inventory);
}

This function works fine, but return the entire structure as json. For example, for the parentId : 4 :

{
    parentId : 4,
    dataInv : {
        {
            testId : "4",
            lang : "en",
            subTask : {
                inv : {
                   hasright : true,
                   isNew : false
                }
            }
        }
    }
}

But i want to retrieve only the subJson "inv" :

inv : {
    hasright : true,
    isNew : false
} 

How can i do to retrieve only this json 'inv' by making the filter directly in the first request ? :

$inventory = Inventory::where('parentId', $parentId)->first();



via wawanopoulos

Advertisement