Laravel whereJsonContains query

user_19408280 0
Viewed 34

I have following JSON code in my database table

    [
  {
    "indentifier": "category",
    "name": "XXXXXXX",
    "products": [
      {
        "indentifier": "childproduct",
        "name": "XXXX",
        "price": 15,
        "sku": "+0094",
        "factorynumber": "156670",
        "unit": "50 Stk/Pck",
        "kp": "50",
        "discountCode": "V",
        "from": "2020-01-01",
        "to": "2099-01-01"
      },
      ...
    ]
  },
  {
    "indentifier": "category",
    "name": "XXXXXXX",
    "products": [
      {
        "indentifier": "childproduct",
        "name": "XXXX",
        "price": 29,
        "sku": "+0104",
        "factorynumber": "156680",
        "unit": "50 Stk/Pck",
        "kp": "50",
        "discountCode": "V",
        "from": "2020-01-01",
        "to": "2099-01-01"
      },
      ....
    ]
  }
]

Now how I can implement for searching the sku in this JSON in "when" keyword? My following code is here:

        $date =  now()->toDateString();
        if ($request->keyword != "") {
            $keyword = $request->keyword;
        } else {
            $keyword = false;
        }

$products = Product::whereJsonContains('catalogs', intval($request->catalog))
            ->whereDate('from', '<=', $date)
            ->whereDate('to', '>', $date)
            ->orderBy('category', 'ASC')
            ->orderBy('sort', 'ASC')
            ->where('active', 1)
            })
            ->when(
                $keyword,
                function ($query, $keyword) use ($category) {
                    $query->where('sku', 'like', '%' . $keyword)
                        ->orWhere('factorynumber', 'like', $keyword)
                        ->orWhere('name', 'like', '%' . $keyword . '%')
                        ->orWhereJsonContains('childs', ['products->sku' => $keyword])
                        ->where('active', 1)
                        });
                }
            )->get();

The line with

->orWhereJsonContains('childs', ['products->sku' => $keyword])

will not work... what is the right syntax for the query? At moment there are 0 results if search for child sku field.

Thanks for helping

laravel mysql database
1 Answers

orWhereJsonContains() doesn't exist in Laravel, but whereJsonContains(), and it works with arrays and not the associative arrays. Also you have to make sure the data from json goes as the first argument, not the second like this:

whereJsonContains('table_column_name_with_json_in_it->json_value_key',$requiredValue)

In that case this query of yours:

->orWhereJsonContains('childs', ['products->sku' => $keyword])

Should be something like this:

->WhereJsonContains('product->sku', $sku)
user_121385920
Related