Laravel 9 how to best query JSON object in JSON column?-CodePudding

I've got a basic app up and running in the latest version of Laravel 9 that's utilising JSON columns for storing certain bits of data. I have a job_type_rates column on my Client model/table, where some have a value similar to:

[
  {
    "job_type": "8",
    "pay_rate": "15.45",
    "charge_rate": "18.45",
    "awr_pay_rate": "21.33",
    "awr_charge_rate": "26.77"
  }
]

What I would like to do is select all clients that have a job_type of 8. I've tried to do Client::whereJsonContains('job_type_rates->job_type', "8")->get() but no results are returned, however that code would work if I didn't have an object in the column.

One way I can get around this is to create a pivot table and go down that route, but I was wondering if anyone had come up against this before and perhaps used a closure or similar?

CodePudding user response：

Based on the comment by @RiggsFolly I tried this code:

Client::whereJsonContains('job_type_rates', ["job_type" => "8"])->get()

And it works, it returns the expected results. As far as I'm aware this isn't in the Laravel docs (which mostly show single value examples).

I think it's still better to extract this out into a pivot table or similar, but I hope this helps someone!