Searching in a column using mysql json data

[dgillier]

Hi,

I'm trying to search in a Column with a value coming from a mySQL json field:

            Column::make(__("cruds.place.fields.city"), "extra_info->city")
                ->collapseOnMobile()
                ->format(fn($value, $row, $column) => $row->extra_info['city'] ?  '<span class="badge bg-blueGray-200">'.$row->extra_info['city']. '</span>':'' )
                ->html()
                ->sortable(),

with no search result returns ...

If I add a function in searchable like:

           Column::make(__("cruds.place.fields.city"), "extra_info->city")
                ->collapseOnMobile()
                ->format(fn($value, $row, $column) => $row->extra_info['city'] ?  '<span class="badge bg-blueGray-200">'.$row->extra_info['city']. '</span>':'' )
                ->html()
                ->searchable(fn(Builder $query, $searchTerm) => $query->orWhere('extra_info->city', 'LIKE', '%'.$searchTerm.'%'))
                ->sortable(),

I also don't have any search results either...

Any idea/suggestions ?
Thanks,
Denis

[lrljoe]

What are you getting in terms of the generated query?

When pasting code, if you can please make sure to enclose your code with ``` on a new line either side, then this keeps the formatting and makes it easier to read.

[dgillier]

Sorry for the code formatting...
Here is the generated SQL:

array:6 [▼ // vendor/rappasoft/laravel-livewire-tables/src/../resources/views/includes/debug.blade.php
  "query" => "select `groups`.*, (select count(*) from `users` inner join `group_user` on `users`.`id` = `group_user`.`user_id` where `groups`.`id` = `group_user`.`group_id` and `users`.`deleted_at` is null) as `users_count`, `groups`.`belong_to_center_id`, `groups`.`id` as `id`, `groups`.`extra_info` as `extra_info`, `groups`.`name` as `name`, `groups`.`short_description` as `short_description`, json_unquote(json_extract(`groups`.`extra_info`, '$."city"')) as `extra_info->city`, `groups`.`active` as `active`, `groups`.`private` as `private`, json_unquote(json_extract(`groups`.`extra_info`, '$."week_day"')) as `extra_info->week_day`, json_unquote(json_extract(`groups`.`extra_info`, '$."recurrence"')) as `extra_info->recurrence`, json_unquote(json_extract(`groups`.`extra_info`, '$."open"')) as `extra_info->open`, json_unquote(json_extract(`groups`.`extra_info`, '$."mixed"')) as `extra_info->mixed`, json_unquote(json_extract(`groups`.`extra_info`, '$."virtual"')) as `extra_info->virtual`, `centers`.`name` as `belongToCenter.name`, `users`.`name` as `createdBy.name`, `groups`.`created_at` as `created_at` from `groups` left join `centers` on `groups`.`belong_to_center_id` = `centers`.`id` left join `users` on `groups`.`user_id` = `users`.`id` where `type` = ? and (`groups`.`name` like ? or `groups`.`short_description` like ? or json_unquote(json_extract(`extra_info`, '$."city"')) LIKE ? or `centers`.`name` like ? or `users`.`name` like ? or `groups`.`created_at` like ?) order by `created_at` desc ◀"
  "filters" => []
  "sorts" => []
  "search" => "paris"
  "select-all" => false
  "selected" => []
]

Thanks for your help :)

[Edim7]

I didn't understand clearly if this part works and display data or do you have issues here already?

Column::make(__("cruds.place.fields.city"), "extra_info->city")
                ->collapseOnMobile()
                ->format(fn($value, $row, $column) => $row->extra_info['city'] ?  '<span class="badge bg-blueGray-200">'.$row->extra_info['city']. '</span>':'' )
                ->html()
                ->sortable(),

You said no search results returns ... and I don't understand if its a ->format() problem or the missing ->searchable() here

For a simpler format I suggest using the $value passed in from "extra_info->city" attribute in that column by defining format like:
->format(fn($value, $row, $column) => $value? '<span class="badge bg-blueGray-200">'.$value. '</span>':'' )

Just to make sure it's not an issue of accessing the right node in json.

If the first part of my suggestion doesn't apply I suggest you break down the complex query you mentioned by searching it directly on the db and trying to see where the issue happens

[dgillier]

Yes, the display part works (and you are right I can change it to your simpler way).

My post is not clear, what I mean is the presence/absence of a searchable() function does not make a difference, no search result whatsoever...

[dgillier]

Well well, found the problem...
Where clause to Json data is case sensitive...
https://sarav.co/case-insensitive-search-in-mysql-json-columns

[lrljoe]

Have you given any thought to creating mysql virtual columns to index your common JSON values? I've always found that to be quite effective for searching and obviously makes retrieval a lot easier! With 8.0 you can just use a functional index, and it still comes with the hidden virtual column.

[dgillier]

Great Idea, thanks :)