# Multi-Value Hash Index (JSON) Multi-Value Hash index optimizes lookups within JSON objects and arrays. It can efficiently find rows where a JSON array contains a specific value or where a specific key-path holds an exact value match. For example, if an **orders** table has a JSON column **details** with the following data: ```JSON {"product_id":[{"id":1001,"type":"flour"},{"id":1002,"type":"yeast"}]} ``` An index created on the **details** column at the path **\["product\_id"**, **"id"]** enables exceptionally fast lookups for **orders** by **product\_id.id**. To index values at the top level in an array, specify an empty path array, such as, `"PATH":[]`. For example, if you have a column containing a top-level JSON array like `[1,2,3]`, you can accelerate searches for values within the array. The multi-value hash index stores pairs of values along with the list of row IDs where that value occurs. Since each value is stored only once, the index remains compact even when values are repeated across many rows. This index is fundamentally a hash index, leveraging the same high-performance technology as the regular columnar hash indexes. ## Syntax ```sql -- Adding the multi-value index to a new table -- CREATE TABLE     ( JSON NULL,     MULTI VALUE INDEX(col) INDEX_OPTIONS=''); -- Adding the multi-value index an to existing table column -- ALTER TABLE ADD MULTI VALUE INDEX (col) INDEX_OPTIONS=''; ``` where `` is a JSON object to specify the tokenizer and other metadata related to it. ```sql '{"TOKENIZER":"MATCH_ANY", "PATH":[]}' ``` where `` is a comma-separated list of values that specify the path to the field that needs to be indexed. If an empty path array, such as, `"PATH":[]`, is specified, then the top-level elements in the array are indexed (such as \[1,2,3]). ## Using the Multi-Value Hash Index The multi-value hash index can optimize queries that use the [JSON\_MATCH\_ANY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-match-any.md) function. This function checks for the existence of values inside JSON data and can be used to find values that are valid JSON types (object, array, integer, float, string, boolean, or JSON `null`) > **📝 Note**: To use the multi-value hash index, the [filter predicate](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-match-any/#section-idm234523191190095.md) in the `JSON_MATCH_ANY` function must use the `MATCH_PARAM_JSON` expression.When matching string values with `JSON_MATCH_ANY` and `MATCH_PARAM_JSON`, string values must be JSON strings with quotes.Use either of the following to create JSON strings:- Use [TO\_JSON](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/to-json.md) to convert a string to a JSON string. > - Manually specify a quoted constant string literal, for example `'"val"'`. **Note**: Typecasting to JSON using `:>JSON` does not automatically wrap an unquoted string into a JSON string. (However, casting works to convert numeric types to their respective JSON type. For example, `2.5:>JSON` converts `2.5` into a JSON double.) **Supported Query Shapes** ```sql JSON_MATCH_ANY(MATCH_PARAM_JSON() = , ) JSON_MATCH_ANY(MATCH_PARAM_JSON() <=> , ) JSON_MATCH_ANY(MATCH_PARAM_JSON() IN (, ..), '') ``` **Shorthand Syntax** You can also use JSON shorthand syntax (`::` operators) in `JSON_MATCH_ANY` predicates. ```sql JSON_MATCH_ANY(::?, MATCH_PARAM_JSON() = ) JSON_MATCH_ANY(::?, MATCH_PARAM_JSON() <=> ) JSON_MATCH_ANY(::?, MATCH_PARAM_JSON() IN (, ..)) ``` `` can be a constant literal (such as a string, integer, float), a typecast operator (`:>`, `!:>`), a user-defined variable (UDV), a deterministic built-in expression (most built-in functions, except for non-deterministic ones like `UUID()` or `RAND()`), or a combination of these. In general, the lookup value must remain constant during query execution. If the value changes per row, such as a column reference, then the index cannot be effectively used and the query falls back to a regular table scan. The index is only used for queries that match the exact `` defined during index creation. For example, if the index is created on **addresses::city**, the query benefits from the index only when filtering values specifically under this path. It is not applied to deeper paths like **addresses::city::district** or to sibling keys like **addresses::zip**. To apply the index to any other path, you must create a separate index. To check whether the index was used for your query filter, run an `EXPLAIN` query - the phrase **multi-value hash index** in the predicate under **ColumnStoreFilter** indicates that the index was used.. ```sql EXPLAIN SELECT col FROM t WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON() = TO_JSON('type'), col, 'key1'); ``` ```output +--------------------------------------------------------------------------------------------------------------+ | EXPLAIN                                                                                                      | |+-------------------------------------------------------------------------------------------------------------+ | Project [t.col]                                                                                              | | ColumnStoreFilter [JSON_MATCH_ANY(MATCH_PARAM_JSON() = TO_JSON('type'),t.col,'key1') multi-value hash index] | | ColumnStoreScan db.t, SORT KEY __UNORDERED () table_type:sharded_columnstore                                | |+-------------------------------------------------------------------------------------------------------------+ ``` ## Remarks * Multi-value hash index is case-sensitive for JSON types. * Multi-value hash index for JSON activates when matched using the following operators: `=`, `<=>`, and `IN()`. * The following table lists the expressions that support Multi-Value Hash Index for each of these operators: | Operator | Supported Expression | | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `=`,`<=>` |
  • Constant literals, for example strings, NULL, DOUBLE, INT, etc.
  • Typecast operators :> and !:>, for example, 100001:>JSON.
  • User-defined variables (UDVs), for example: 
    SET @obj = '{"city": "New York"}':>JSON;  SELECT a:>JSON FROM t  WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON()=@obj, a, 'city');
  • Procedural SQL variables (stored procedure arguments and local variables).
  • Nested constant (deterministic) built-in expressions, for example: 
    JSON_BUILD_ARRAY(JSON_SET_JSON('{}', 'a', @json), '{"a":4}':>JSON)
  • A combination of all of the above.
| | `IN()` |
  • Parameterized IN lists with:
    • Literals of the same types
    • Single argument built-in expressions of the same shape and literal types in the list, for example, IN(HEX('a'), HEX('b')).
| ## Limitations * The [filter predicate](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-match-any/#section-idm234523191190095.md) in `JSON_MATCH_ANY` must use the `MATCH_PARAM_JSON` expression. * Only runtime constant expressions are supported for matching (i.e. UDV, builtins, typecasts, PSQL variables are supported; UDFs and column fields are not supported). * Using `OR` inside a `JSON_MATCH_ANY` predicate (for example, `JSON_MATCH_ANY(MATCH_PARAM_JSON() = 12 OR MATCH_PARAM_JSON() = 1, a, 'a'))` disables index usage. Instead, use either an `IN` list or multiple `JSON_MATCH_ANY` filters. * Only parameterized `IN` list is supported. * Indexing multiple key paths for the same JSON field requires separate indexes, for example, to index both **a::k1** and **a::k2**, you must create two indexes. * This index is supported only for columnstore tables. * Multi-value index for JSON does not support the following: * References to other unindexed fields on the right-hand side of an expression. * Non-deterministic built-in functions, for example `RAND()`. * `JSON_MATCH_ANY` predicate with `MATCH_ELEMENTS` option. * `!=` and other comparison operators (excluding `=` and `<=>`). * `NOT IN` lists. ## Examples ## Example 1- Searching `users` by `city` Consider the following table definition: ```sql CREATE TABLE users (     id BIGINT PRIMARY KEY AUTO_INCREMENT,     created DATETIME(6) DEFAULT NOW(6),     data JSON ); ``` User records are stored in the `data` column as JSON documents with the following structure: ```sql INSERT INTO users (data) VALUES ('{"id":1,"name":"Alice","addresses":[{"street":"123 Main St","city":"New York","zip":"10001"},{"street":"456 Oak Ave","city":"Los Angeles","zip":"90001"}]}'), ('{"id":2,"name":"Bob","addresses":[{"street":"789 Pine Ln","city":"Chicago","zip":"60601"}]}'), ('{"id":3,"name":"Charlie","addresses":[{"street":"101 Elm St","city":"New York","zip":"10002"},{"street":"222 Maple Dr","city":"Houston","zip":"77001"}]}'); ``` Adding a multi-value Hash Index to the path "**addresses, city**" can significantly improve lookup performance. ```sql ALTER TABLE users ADD MULTI VALUE INDEX (data)     INDEX_OPTIONS='{"TOKENIZER":"MATCH ANY","PATH":["addresses", "city"]}'; ``` Find all `users` who live in Houston using the `JSON_MATCH_ANY` built-in function. ```sql SELECT id, data::name FROM users WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON()='"Houston"', data, 'addresses', 'city');  ``` ```output +----+------------+ | id | data::name | +----+------------+ |  3 | "Charlie"  | +----+------------+ ``` The following example rewrites the previous query using shorthand syntax: ```sql SELECT id, data::name FROM users WHERE JSON_MATCH_ANY(data::?addresses.city, MATCH_PARAM_JSON() = '"Houston"'); ``` ```output +----+------------+ | id | data::name | +----+------------+ | 3 | "Charlie" | +----+------------+ ``` To verify that the multi-value hash index was used, run the `EXPLAIN` query as: ```sql EXPLAIN SELECT id, data::name FROM users WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON()='"Houston"', data, 'addresses', 'city'); ``` ```output +-----------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------+ | Gather partitions:all alias:remote_0 parallelism_level:segment | | Project [users.id, JSON_EXTRACT_JSON(users.data,'name') AS `data::name`] | | ColumnStoreFilter [JSON_MATCH_ANY(MATCH_PARAM_JSON() = '\"Houston\"',users.data,'addresses','city') multi-value hash index] | | ColumnStoreScan db1.users, SORT KEY __UNORDERED () table_type:sharded_columnstore | +-----------------------------------------------------------------------------------------------------------------------------+ ``` ## Example 2 - Multiple indexes In this example, two separate multi-value hash indexes are used to optimize common lookup patterns on a JSON column for the following two fields: * An integer field **eventId** * A string array **tags** These indexes help speed up queries that filter by **eventID** and check for specific tags without scanning the entire table. 1. Create a table. ```sql CREATE TABLE events ( id BIGINT PRIMARY KEY, created DATETIME(6), payload JSON ); ``` 2. Add multi-value hash indexes via `ALTER TABLE`. ```sql ALTER TABLE events ADD MULTI VALUE INDEX idx_event_id (payload) INDEX_OPTIONS = '{"TOKENIZER": "MATCH ANY", "PATH": ["eventId"]}'; ALTER TABLE events ADD MULTI VALUE INDEX idx_tags (payload) INDEX_OPTIONS = '{"TOKENIZER": "MATCH ANY", "PATH": ["tags"]}'; ``` 3. Insert data into the table. ```sql INSERT INTO events VALUES (1, NOW(6), JSON_BUILD_OBJECT('eventId', 101, 'tags', JSON_BUILD_ARRAY('sports', 'outdoor', 'fun'))), (2, NOW(6), JSON_BUILD_OBJECT('eventId', 202, 'tags', JSON_BUILD_ARRAY('music', 'concert'))), (3, NOW(6), JSON_BUILD_OBJECT('eventId', 101, 'tags', JSON_BUILD_ARRAY('sports', 'indoor'))); ``` 4. Query using both the indexed fields: ```sql SELECT * FROM events WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON() = 101:>JSON, payload, 'eventId') AND JSON_MATCH_ANY(MATCH_PARAM_JSON() = TO_JSON('sports'), payload, 'tags'); ``` ```output +----+----------------------------+---------------------------------------------------+ |id | created | payload | +----+----------------------------+---------------------------------------------------+ | 1 | 2025-04-29 15:36:52.797432 | {"eventId":101,"tags":["sports","outdoor","fun"]} | | 3 | 2025-04-29 15:36:52.797432 | {"eventId":101,"tags":["sports","indoor"]} | +----+----------------------------+---------------------------------------------------+ ``` The following example rewrites the previous query using shorthand syntax: ```sql SELECT * FROM events WHERE JSON_MATCH_ANY(payload::?eventId, MATCH_PARAM_JSON() = 101:>JSON) AND JSON_MATCH_ANY(payload::?tags, MATCH_PARAM_JSON() = TO_JSON('sports')); ``` ```output +----+----------------------------+---------------------------------------------------+ | id | created | payload | +----+----------------------------+---------------------------------------------------+ | 3 | 2026-04-02 17:36:43.595620 | {"eventId":101,"tags":["sports","indoor"]} | | 1 | 2026-04-02 17:36:43.595620 | {"eventId":101,"tags":["sports","outdoor","fun"]} | +----+----------------------------+---------------------------------------------------+ ``` 5. Verify index usage from the query `EXPLAIN`: ```sql EXPLAIN SELECT * FROM events WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON() = 101:>JSON, payload, 'eventId') AND JSON_MATCH_ANY(MATCH_PARAM_JSON() = TO_JSON('sports'), payload, 'tags'); ``` ```output +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather partitions:all alias:remote_0 parallelism_level:segment | | Project [events.id, events.created, events.payload] | | ColumnStoreFilter [JSON_MATCH_ANY(MATCH_PARAM_JSON() = (101:>JSON COLLATE utf8mb4_bin NULL),events.payload,'eventId') multi-value hash index AND JSON_MATCH_ANY(MATCH_PARAM_JSON() = TO_JSON('sports'),events.payload,'tags') multi-value hash index] | | ColumnStoreScan db1.events, SORT KEY __UNORDERED () table_type:sharded_columnstore | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ``` *** Modified at: June 18, 2026 Source: [/db/v9.1/create-a-database/multi-value-hash-index-json/](https://docs.singlestore.com/db/v9.1/create-a-database/multi-value-hash-index-json/) (An index of the documentation is available at /llms.txt)