JSON column - find any matching value

Is your JSON column an object or an array?

You wont be able to do that with the query builder since it translates to SQL.

Instead you can do your filtering through PHP.

Model::all()->filter(function($model) {

            return strpos(json_decode($model->mycolumn)->myattribute, 'searchterm') !== false;
        });

@staudenmeir it's an object - which in turn contains lots of other nested objects. It's pretty much a dump of $request->all() for some complicated forms with 100's of fields.

@Dchubb ah - I was hoping the new json syntax in mysql would let me do it natively :-/ Fetching all the records and filtering them would be a bit 'costly' as there are many, many thousands of them :-/

My hacky way around it would be to store the data as one big concatenated text column and do a like query against that - but I'd much rather avoid that if I can...

You could use a raw query:

Model::whereRaw('json_field->"$[*].*" like ?', "%$searchterm%")->get()

You can only do that with raw query as said @36864 . Look at MySQL documentation.

@36864 @bestmomo ah - fair enough I guess. Thanks for the pointer - I can avoid my hacky text column now and feel a little less guilty when I look at the code :-) Thanks again :-)

For anyone else who needs to do this, this is what I've got working on the model (assuming your json column is called 'data') :

public static function searchFor(string $term)
{
    return static::whereRaw("JSON_SEARCH(data, 'all', ?) IS NOT NULL", ["%{$term}%"])->get();

}

Then elsewhere you can do :

$results = Model::searchFor('hello');