Laravel json column query to check if object exists

Question

So I have a json column called settings in the table companies (which has a Laravel model called Company). That column may or may not contain an object called business_key. I try to return only the companies that contain this object. My query looks like this:

$owners = \Company::where('settings->business_key')->get();

I don't know how to make something like this work. I did not find anything Laravel specific for this. Thank you for your time and help!

If you have no default value for that column, it will be null in case of no business key. So, retrieve all column with not null business key — Sagar Gautam
– Sagar Gautam, Commented Jul 3, 2017 at 17:17

cfkane · Accepted Answer · 2017-07-03 17:22:07Z

12

Assuming you are casting the field as an array in the model, you can treat it like other larval fields, so your query would be:

$owners = \Company::whereNotNull('settings->business_key')->get();

answered Jul 3, 2017 at 17:22

cfkane

7531 gold badge7 silver badges20 bronze badges

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

2 Comments

Eric McWinNEr Over a year ago

Hey, I have a problem too, in my case I have an JSON array of roles like ["user", "admin", "customer"] in this case how can I do a where clause for if a user is an admin?

Eric McWinNEr Over a year ago

So I figured it out, a simple Model::whereJsonContains["roles", "user"] fixed it...Yaaaay!

vivien · Accepted Answer · 2019-12-16 17:00:19Z

1

In the purpose to retrieve all not null data, you can try this :

$owners = \Company::whereRaw('JSON_EXTRACT(settings, "$.business_key") <> CAST("null" AS JSON)')->get();

answered Dec 16, 2019 at 17:00

vivien

111 bronze badge

Comments

Sagar Gautam · Accepted Answer · 2017-07-03 17:23:13Z

0

Try this one,

$data = \Company::whereNotNull('setting')->get();

If setting column contains no object then value will be NULL, then above query gives all records with objects in setting column. See docs here

answered Jul 3, 2017 at 17:23

Sagar Gautam

9,4099 gold badges62 silver badges95 bronze badges

Collectives™ on Stack Overflow

Laravel json column query to check if object exists

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related