JSON_CONTAINS in MySQL for Schema-Free Array Searches

MySQL’s JSON_CONTAINS function lets you query JSON array columns without rigid schema changes. Need to filter rows based on values inside a JSON array? Use:

WHERE JSON_CONTAINS(config_json, '"USD"')

Notice the double quotes inside single quotes — JSON requires double-quoted strings. This searches for "USD" anywhere in the array.

When to Use JSON Columns

JSON columns are ideal for flexible metadata that doesn’t need structured querying. For example:

• Feature flags per user (varies by account type)
• Configuration options per tenant
• Tags or labels that change frequently

-- Find all users with "premium" feature enabled
SELECT * FROM accounts
WHERE JSON_CONTAINS(features, '"premium"');

The Performance Trade-Off

JSON searches cannot use regular indexes. For frequently-queried paths, consider generated columns with indexes:

-- Add a generated column for faster searches
ALTER TABLE accounts 
ADD feature_list VARCHAR(255) 
AS (JSON_UNQUOTE(JSON_EXTRACT(features, '$.enabled'))) STORED;

-- Index it
CREATE INDEX idx_features ON accounts(feature_list);

This gives you JSON’s flexibility with traditional index performance. Laravel can generate these via migrations:

Schema::table('accounts', function (Blueprint $table) {
    $table->string('feature_list')
          ->storedAs("JSON_UNQUOTE(JSON_EXTRACT(features, '$.enabled'))")
          ->index();
});

Use JSON for truly flexible data, but add generated columns + indexes for hot paths.