Searching Values Inside a MySQL JSON Array Efficiently

An array inside a JSON document is the usual home for tags, roles, categories and the lines of an order, and the question that follows is always the same: which rows have a particular value in that array. MySQL offers four functions that look interchangeable. They differ in what they match, how they treat types and, above all, whether an index can help. This article compares them on two tables, shows the multi-valued index that makes them fast, and lists the matching rules that decide whether you get the rows you expect.

The four functions

**value MEMBER OF(array)** is true if the array has the value as an element. It is the simplest and the one the optimizer understands best:

select count(*) from products where 'gift' member of (attrs -> '$.tags');

**JSON_CONTAINS(target, candidate)** is true if everything in candidate appears in target. With a candidate array it means "contains all of these":

select count(*) from big_orders where json_contains(doc -> '$.items', '{"sku":"S7"}');

That form finds orders with an item that contains the object {"sku":"S7"}, so it can match on a subset of the keys: it counted 5,000 orders, and adding "qty":3 to the candidate changed nothing here only because every S7 item in my generated data has quantity 3. For all of several values, pass an array of candidates: json_contains(doc -> '$.items', '[{"sku":"S7"},{"sku":"S8"}]') counted 4,000 orders, the ones where each candidate is found in some item.

**JSON_OVERLAPS(a, b)** is true if the two share at least one value, which gives "any of these": json_overlaps(doc -> '$.items[*].sku', '["S7","S8"]') counted 6,000 orders.

**JSON_SEARCH(doc, 'one'|'all', string [, escape [, path]])** looks for a string and returns the path where it was found, or NULL. It is the only one that does pattern matching, with % and _ wildcards:

json_search('{"a":"x","b":["y","xx"]}', 'all', 'x')    "$.a"
json_search('{"a":"x","b":["y","xx"]}', 'all', 'x%')   ["$.a", "$.b[1]"]
json_search('{"a":"X"}', 'one', 'x')                    NULL
json_search('{"a":1}', 'one', '1')                      NULL

Exact match is the default and the pattern needs the wildcard. The search is case sensitive and does not look at numbers, which is how '1' failed to find the integer 1. Use it to find a string in an unknown place, and for nothing else.

What they match: the type rules

JSON values compare by type and value, and the SQL value you pass is converted first.

• Numbers match numbers regardless of how they are written: 1 member of ('[1,2]') and 1.0 member of ('[1,2]') were both 1, and json_contains('[1,2]', '1.0') was 1.

• A string does not match a number: '1' member of ('[1,2]') was 0, and json_contains('[1,2]', '"1"') was 0. This is the quiet source of "it works for ids in the test data and not in production": if an id is a number in some documents and a string in others, one search matches only one kind.

• Objects match by containment for JSON_CONTAINS and by equality for MEMBER OF: cast('{"a":1}' as json) member of ('[{"a":1}]') was 1, but an element with extra keys would not match.

• Nested arrays are matched by JSON_CONTAINS at depth: json_contains('[1,[2,3]]', '2') was 1 and so was '[2]', since containment recurses into nested arrays. MEMBER OF only compares top-level elements.

• Strings are case sensitive and use the utf8mb4_bin collation.

When the candidate is a JSON string, remember its quotes. json_contains(attrs -> '$.tags', '"gift"') has the inner double quotes, because the second argument is JSON text; 'gift' would be an invalid JSON document and raise an error.

The path-in-argument rule

JSON_CONTAINS(doc, candidate, path) takes an optional path, which saves the arrow. It does not accept wildcards: json_contains(doc, '"S7"', '$.items[*].sku') failed with ERROR 3149: In this situation, path expressions may not contain the * and ** tokens or an array range. The workaround is to extract the array with the wildcard first and pass it as the target: json_contains(doc -> '$.items[*].sku', cast('"S7"' as json)) worked (the cast gives a JSON value the function can use directly). The same expression with MEMBER OF, 'S7' member of (doc -> '$.items[*].sku'), is shorter and was the faster of the unindexed forms.

This array-from-a-wildcard trick is the central idiom for arrays of objects: doc -> '$.items[*].sku' returns ["S3","S4","S5","S6","S7"], a flat array of the values you want to search, and every function above applies to it.

Speed without an index

On the 50,000 big_orders documents, searching for one SKU that appears in 5,000 of them, with a table scan:

'S7' member of (doc -> '$.items[*].sku')                             59 ms
json_contains(doc -> '$.items', '{"sku":"S7"}')                       94 ms
json_search(doc, 'one', 'S7', null, '$.items[*].sku') is not null     198 ms

MEMBER OF is the cheapest check per row, JSON_CONTAINS costs more because it compares objects, and JSON_SEARCH is the slowest as it walks and matches strings. On products, a search for one tag took 135 ms with JSON_CONTAINS and a path argument, 95 ms with JSON_SEARCH, 118 ms with a LIKE on the JSON text and 258 ms with JSON_CONTAINS over attrs -> '$.tags'. The numbers vary with the document, but the pattern holds: all of them read every document.

Do not use LIKE '%"gift"%' on the JSON text as a shortcut. It was no faster than the functions (118 ms) and it matches the string anywhere, including inside other values and in keys. It is wrong in a way that will not fail a test.

A multi-valued index

Since 8.0.17 you can index every element of an array in one index:

create index idx_item_sku on big_orders ((cast(doc -> '$.items[*].sku' as char(10) array)));

The cast(... as ... array) is what makes it multi-valued: one row can add several index entries, one per element. The plan for 'S7' member of (doc -> '$.items[*].sku') changed to Index lookup on big_orders using idx_item_sku and the time dropped from 59 ms to 17.6 ms for 5,000 matching orders, 10 percent of the table. JSON_CONTAINS with a scalar candidate used Index range scan ... over ('S7' MEMBER OF ...), and JSON_OVERLAPS for two values used an index range scan with an OR of two MEMBER OF ranges and took 38.7 ms, against a table scan of 303 ms for the tag overlap on products without a usable index.

Some rules for these indexes:

• The query must match the index expression closely: the same path, the same cast type. 'S7' member of (doc -> '$.items[*].sku') uses it; a different path does not.

• Only three functions are served: MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS. JSON_SEARCH and LIKE never use it.

• The element type is part of the index. char(10) means each SKU is cast to at most 10 characters, and a longer value makes the insert fail. Choose a width that fits the data, or use unsigned for numbers.

• Limits apply. Each index can have one multi-valued part, and there are limits on the number of values per row. A very long array makes a heavy index. In my case the index for 5 elements was small.

• You pay at write time. Inserts and updates of the document add and remove an entry for each element.

When a row also has a selective condition, the optimizer picks one access path: for 'S7' member of (...) and doc -> '$.customer.id' = 7 it used the multi-valued index to find the 5,000 candidates and filtered the customer afterward, which took 17.5 ms and returned zero rows. A generated column and a composite index on the customer could do better if that combination is common.

Which to use

• One value: MEMBER OF, with a multi-valued index if the table is large.

• All of several values: JSON_CONTAINS with an array candidate.

• Any of several values: JSON_OVERLAPS.

• A string by pattern, or in an unknown location: JSON_SEARCH.

• Elements of an array as rows for reports and joins: JSON_TABLE (using JSON_TABLE to turn MySQL JSON into rows).

For the paths you pass, see JSON_EXTRACT and the arrow operators in MySQL; the overall picture is in using the JSON column type in MySQL. To inspect an array while you work out the path, the JSON formatter shows the structure in the browser.

When searching the array is the wrong design

You search by one value all the time. Then you are using the array as an index. A table order_items(order_id, sku) with an ordinary index answers the question with better statistics and a foreign key.

The array holds objects you filter by several fields. JSON_CONTAINS can test "an item with sku S7 and qty 3" as one object, but it cannot say "qty above 3". For ranges, unpack with JSON_TABLE or store rows.

Elements are updated individually. Changing one element rewrites the document, and two sessions changing different elements collide.

The array is large. Thousands of elements means a multi-valued index with thousands of entries per row and slow updates.

You need counts by value. Counting how many documents have each tag is a JSON_TABLE aggregation over every document. If it is a report you run often, materialize it.

The functions are defined in the manual: MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS, and the index rules in multi-valued indexes.