CODEWITHSUNDEEP
mysqljsonlaravel
Emjey23
Emjey23

Reputation: 367

Laravel MySQL Select from nested JSON-column

I have JSON-column in MySQL database.

Controller

This is my sample code that gets the selected_products from the table and returns it as JSON which I intend to use for chart data. What I am trying to do is get the name of the product.

$products = Order::select('selected_products')->get();

return response()->json(['test_data' => $products], 200);

The JSON response may view here

If I do it like this:

$products = Order::select('selected_products->name')->get();

It returns an error of:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."name"' from orders' at line 1 (SQL: select selected_products->'$."name"' from orders)

I hope someone can help me. Thank you.

Upvotes: 1

Views: 474

Answers (2)

Iftikhar uddin
Iftikhar uddin

Reputation: 3182

Why don't you loop through $products array ?

$porductsNames = array();

$products = Order::select('selected_products')->get();

foreach ($products as $product){
   foreach($product('selected_products') as $p){
        $productsNames[] = $p['name'];
   }
}

// Then you can use it
dd($productsNames);

Upvotes: 1

Hasitha Amarathunga
Hasitha Amarathunga

Reputation: 2005

Replace the $products as following line and check it work.

$products = Order::all('selected_products');

If it's not possible use the sql statement like below

$products=DB::select("SELECT selected_products from orders")

Upvotes: 0

Related Questions