query builder search a json column

Posted 1 week ago by calin.ionut

Hello!

In the product table a have a column Colors where i saved the product colors as a json (the colors id`s)

Example of data saved in a column Colors:

[{"id":"2"},{"id":"4"},{"id":"5"},{"id":"6"}]

The query i want to make:

$products = Product::query();
if(request()->has('color')){
    $color = ProductColor::where('Slug', request('color'))->first();
    $products = $products->where('Colors->"$.id"', $color->ColorID);
}

but does not work (it doesn`t display any product)

I also tried with the JSON_EXTRACT and not working.

$products = $products->where(DB::raw('JSON_EXTRACT(`Colors`, "$.id")'), '=', $color->ColorID);

Wow can i find a value in a json column?

Please sign in or create an account to participate in this conversation.