Using WhereJsonContains with an array

I think it could be like this

$users = User::when($request->get('positions'), function($query) use ($request) {
    $query->whereJsonContains('positions->name', $request->get('positions'));
})->get();
 
return UserResource::collection($users);

Hi! Unfortunately it isn't working. This is because positions is not an object, but an array.

This code allows me to check for each position where the name is equal to the position.

                $users->whereJsonContains('positions', ['name' => $position])->get();

This is perfectly fine as is, but it can only accept 1 string. I need to somehow find a way for something like this to work

                $users->whereJsonContains('positions', ['name' => $request->get('positions')])->get();

But as I have said, this will simply not work. Hence, I'm finding a way to combine whereIn and whereJsonContains.

Thank you!

@ignisrzeus Do you have this in your model?

protected $casts = [
    'positions' => 'array',
];

It should work what I posted before.

For example when you try this, it should work in my opinion:

$data = ["Project Execution Head", "Some Other Position"];

$users = User::when($data, function($query) use ($data) {
    $query->whereJsonContains('positions->name', $data);
})->get();
 
return UserResource::collection($users);

Yes I do.

Below is an example of the positions column of a user in the database:

[{"name":"Project Execution Head","job_level":"1A"},
{"name":"Standing Officer","job_level":"1A"}]

Hope this somewhat helps. Thanks!

And just to be clear this isn't working unfortunately.

Any advice would be an immense help. Thank you!

@ignisrzeus In example I forgot there change it to $data, can you try it again?

Which part of it should I change to $data?

Also, how will this code work?

    $query->whereJsonContains('positions->name', $data);

In my understanding, this will not work because positions is an array, not an object. Hence, positions does not have a property of name per se. On the other hand,

    $query->whereJsonContains('positions', ['name' => $position])->get();

works as intended, only IF i compare it to 1 string. However, comparing it to multiple strings like in an array will yield me nothing. Please do let me know if my understanding of whereJsonContains is incorrect. Thank you

Try this again:

$data = ["Project Execution Head", "Some Other Position"];

$users = User::when($data, function($query) use ($data) {
    $query->whereJsonContains('positions->name', $data);
})->get();
 
return UserResource::collection($users);

Here you can find it wors with multiple values.

Yes you set it to array in casts property of your model, which means you can use it. Because you have array of object in yout positions property, which is json and it should work.

Unfortunately this still does not work. It returns an empty collection of Users.

In addition, I think the difference between the example provided from the documentation is that "options" is posted as a JSON Object I believe.

options:{
.... => ....
.... => ....
.... => ....
language => ['en', 'jp']
}

I am able to perform the above example IF I have a JSON object stored in the database, not a JSON Array of Objects. Hopefully I make sense.

I appreciate your help. Please do let me know if there are other tips you can give me.

Also, one more thing. I tried to somehow bruteforce my way to the solution by using this

$users = User::query();
        if($request->get('positions')){
            $positions = $request->get('positions');

            foreach($positions as $position){
                $users->whereJsonContains('positions', ['name' => $position]);
            }
   
        }
        return UserResource::collection($users->get());

This returns an empty collection of users unfortunately if my positions are ["Project Execution Head", "Some Other Position"]; However, if i delete one of these elements, it will return me a a collection of users where their positions column contains one or the other.

This doesnt make sense to me, because it works if the array only has 1 element in it, but if i put 2 or more elements, it will return me an empty collection of users.

Was hoping if you would know the answer to this question as well. I apologize for the inconvenience and thank you very much!

@ignisrzeus This could help you.

Then it could be

$positions = $request->get('positions');

$users = User::when($positions , function($query) use ($positions) {
    $query->where(function ($query) use ($positions) {
        foreach($positions as $position) {
            $query->orWhereJsonContains('positions', ['name' => $position]);
        }
    });
})->get();
 
return UserResource::collection($users);

Thank you for linking the post to me! It's actually insane lol.

My Previous Code:

  $positions = $request->get('positions');
            foreach($positions as $position){
                $users->whereJsonContains('positions', ['name' => $position]);
            }

It doesn't work because i should have been using orWhereJsonContains instead!

This yields the correct answer.

  $positions = $request->get('positions');
            foreach($positions as $position){
                $users->orWhereJsonContains('positions', ['name' => $position]);
            }

CONCLUSION: Always use orWhereJsonContains when Looping through an array of values. Will now close this post. Thank you for your help!

@ignisrzeus Mark best reply as yours?

Just a side note, there is no need to for the query function to exist. It can be as simple as

$users = User::query();
$positions = $request->get('positions');
	foreach($positions as $position){
                $users->orWhereJsonContains('positions', ['name' => $position]);
            }

Thank you once again for your help.

Yes, in this case you can use it like you. I prefer use when instead of if.