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

Question

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?

You have an object in an array, but you are not referencing the array — RiggsFolly
– RiggsFolly, Commented Oct 13, 2022 at 7:36
@RiggsFolly that's what I can't seem to do through the query though, the Laravel docs don't seem to cover this? — Andy Holmes
– Andy Holmes, Commented Oct 13, 2022 at 7:37
To be fair, it looks like this data should have been stored in a more traditional way in a table, and accessing it would have been simple — RiggsFolly
– RiggsFolly, Commented Oct 13, 2022 at 7:37
@RiggsFolly I know, and I can go down that road as I've said in the question, but I'm wondering if this is possible first — Andy Holmes
– Andy Holmes, Commented Oct 13, 2022 at 7:38
Turns out Client::whereJsonContains('job_type_rates', ["job_type" => "8"])->get() is possible and works, but I think I'll refactor this regardless :) — Andy Holmes
– Andy Holmes, Commented Oct 13, 2022 at 7:45

Andy Holmes · Accepted Answer · 2022-10-13 07:47:07Z

1

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!

answered Oct 13, 2022 at 7:47

Andy Holmes

8,10711 gold badges56 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related