json-search | Asynchronous full text indexing | Search Engine library

After a lot of research of Laravel inner workings, what I asked initially isn't possible.

At the point where you call the method to interact with DB in some way, a query builder instance is made and depending on the method you called on the original model, the query is formed. After execution there is nothing to tap into as the object no longer exists.

Also, from the logical point of view, I have concluded that search makes no sense on already loaded models, so I did a concept switch there and disabled the package for already loaded models.

Try FOR ORDINALITY (see 12.17.6 JSON Table Functions), this type enumerates rows in the COLUMNS clause:

You can use the following double-quote the key name:

JSON_CONTAINS() does more complex work than MEMBER OF.

JSON_CONTAINS() must parse its second argument, a candidate JSON document that you're searching for within the stored JSON document.

The candidate may not be a simple scalar as you're searching for in your example above. It could be a more complex document with its own nested arrays and objects.

Therefore comparing the candidate to the stored document must potentially compare in a more complex manner, not just searching for a single scalar value, but comparing recursively, all nested elements.

Even if your example search is for a simple scalar value, it still invokes the same code path that might need to search for a complex document. Based on your timing measurement, that code path seems to have more overhead.

Whereas MEMBER OF only searches for a scalar value, and only searches in an array. It can also optimize by using a cached, pre-sorted array.

See https://github.com/mysql/mysql-server/blob/8.0/sql/item_json_func.cc#L3852 for the code.

How to emulate JSON_OVERLAPS function on MySQL 5.7?

Edit: Unfortunately, upgrading to MySQL 8 is not an option since we run MariaDB on production, which also doesn't have that function.

Be warned like Strawberry, suggested already upgrading is more easy

Now that is out off the way. You still asked for it, lets have some fun. I posted some answers in the past to simulate MySQL's 8 JSON_TABLE(), why did i mention this? Because i use this method to emulate MySQL's 8 JSON_OVERLAPS to simply JOIN both resultsets which emulate JSON_TABLE() to a final resultset

Which makes the query below (forgive the formatting)

Query

Use ISNULL property to create dummy json

Look at JSON_CONTAINS (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains) and us it in your WHERE clause to identify the records that match your criteria.

The function JSON_EXTRACT in maria db is available since version 10.2.3, you can read the documentation here.

You have to update your maria db installation to use that function.