Add Query Builder support for MySQL MEMBER OF() JSON queries

[smares]

MySQL supports multi-valued indexes on JSON arrays, for example:

ALTER TABLE contracts
    ADD INDEX contracts_feature_names (
        (CAST(configuration->'$[*].name' AS CHAR(64) ARRAY))
    );

These indexes can be used efficiently with MEMBER OF() queries:

SELECT *
FROM contracts
WHERE 'feature' MEMBER OF (configuration->'$[*].name');

Laravel currently provides JSON query methods such as whereJsonContains(), but there does not appear to be a Query Builder method for expressing MEMBER OF() directly. This means using a MySQL multi-valued index like the above currently requires whereRaw():

Contract::query()->whereRaw(
    '? MEMBER OF (configuration->\'$[*].name\')',
    ['feature'],
);

It would be useful to have first-class Query Builder support for this operation, with proper identifier/path handling and parameter binding.

For example, an API could look roughly like:

$query->whereJsonMemberOf(
    'configuration->$[*].name',
    'feature',
);

The proposed argument order here is:

whereJsonMemberOf(string $path, mixed $value)

That is, the JSON column/path comes first and the value to test for membership comes second, consistent with methods such as whereJsonContains($column, $value).

Conceptually, the example above would compile to:

'feature' MEMBER OF (configuration->'$[*].name')

The exact method name and signature could of course be adjusted to better match Laravel's existing Query Builder conventions.

The primary motivation is not only avoiding raw SQL, but enabling idiomatic Laravel queries for MySQL's multi-valued JSON indexes. whereJsonContains() is not always an equivalent replacement, particularly when the indexed expression targets values selected through a wildcard JSON path such as $[*].name.

Ideally, the feature could also support the corresponding orWhere... / negated variants in the same way as Laravel's other JSON query predicates.

[vynazevedo]

There is a path-handling detail to settle before wiring this into the grammar. The proposed configuration->$[*].name is not currently treated as a raw MySQL JSONPath by Laravel's JSON path helper.

I exercised the original CompilesJsonPaths trait on 13.x in an isolated PHP harness, with the supporting string/collection operations stubbed. It produces:

configuration->$[*].name  ->  '$."$[*].name"'
configuration->[*]->name  ->  '$[*]."name"'

The first selects a literal property named $[*].name; the second expresses the wildcard traversal. A new method should either retain Laravel's arrow-path convention or explicitly introduce raw JSONPath support, rather than silently treating the two as interchangeable.

There are also two different JSON operations involved. compileJsonContains() supplies the path as the third argument to JSON_CONTAINS(). MySQL rejects wildcard paths in that argument. Your MEMBER OF() example instead searches the array produced by extracting the wildcard path. MySQL can use multi-valued indexes for JSON_CONTAINS() too, so the useful distinction here is the expression/semantics, not that only MEMBER OF() supports those indexes. MySQL reference.

For a first implementation, I'd keep scalar values bound as parameters and build the RHS as a JSON extraction. I wouldn't reuse the normal wrapJsonSelector() unchanged: it adds json_unquote(). Likewise, reusing JSON_CONTAINS's JSON-encoded candidate binding without checking the resulting SQL type can change string membership semantics.

Tests worth including are integer 17 versus string '17', quoted keys, wildcard paths, SQL NULL, and negation. MySQL does not coerce numeric and string members into one another, and NULL remains NULL under negation. I'd also keep a real MySQL integration test with your exact index expression and EXPLAIN; SQL compilation alone cannot establish index use. I haven't run a MySQL server for this check.