Query Db case sensitive in JSON column

#1

Hi. When I use a where statement and pass through a search query, it is case sensitive. I.e. If the json_data->full_name value is “Chris” and I search “chris” it returns no results! But if I search “Chris” it finds it. Not come across this before, any ideas?

    public function render()
    {
        return view('livewire.backend.form-submissions.submission-index')
            ->layout('layouts.frontend')
            ->with([
                'collection' => FormData::where('json_data->full_name', 'like', '%' . $this->search . '%')
                    ->orWhere('status', $this->status)
                    ->orderBy('created_at', 'desc')
                    ->get()
            ]);
    }
#2

With postgresql you can use 'ilike' instead.

Looking for MySql solution :slight_smile:

#3

Well. Something like:

FormData::query()
->where('json_data->full_name', 'like', '%' . str($this->search)->lower() . '%')
#4

Hey, @cloudwales

Maybe you need to use whereJsonContains?

Take a look here

#5

I don’t know much about json data, but this is how I’ve done it:

   public $search = '';

public function getDb()

    {

        return $this->users = UserData::where('last_name', 'like', '%' . $this->search . '%')

            ->when($this->city, function ($query) {

                return $query->where('city', 'like', '%' . $this->city . '%');

            })

            ->when($this->field, function ($query, $field) {

                return $query->where('field_id', $field);

            })

            ->when($this->academic_degree, function ($query, $academic_degree) {

                return $query->where('academic_degree', $academic_degree);

            })

            ->get();

    }

    public function render()

    {

        $this->getDb();

        return view('livewire.home-page')->extends('layouts.app')->section('content');

    }
#6

Also you can take a look at HERE