# JSON\_MATCH\_ANY Checks for the existence of values within JSON data that match specified filters. It is more succinct than extracting values and checking them using SQL filters. The `JSON_MATCH_ANY` function can be used to search JSON data for JSON values (object, array, integer, float, string, boolean or JSON `null`). The `JSON_MATCH_ANY` function takes a filter path and a filter predicate and returns true if there exists a value in the JSON data at the filter path for which the filter predicate evaluates to true.  The `JSON_MATCH_ANY_EXISTS`  function can be used to search for the existence of values. This function takes a filter path and returns true if there is a value (possibly null) in the JSON at the filter path. ## Syntax ## Longhand Syntax * The `JSON_MATCH_ANY` function returns true if there exists a value in the JSON at the filter path for which the filter predicate evaluates to true. The filter predicate and filter path are described in [Arguments](https://docs.singlestore.com/#section-idm4584041138499233345017882551.md). ```sql JSON_MATCH_ANY(, , ) ``` * The `JSON_MATCH_ANY_EXISTS`  function returns true if there is a value (possibly null) in the JSON at the filter path. ```sql JSON_MATCH_ANY_EXISTS(, ) ``` ## Shorthand Syntax * `filter_predicate` is the same as in the longhand syntax. * `filter_path` is a dot-separated list of keys (as opposed to a comma-separated list of arguments in the longhand syntax). * `::?` indicates the following period-separated values are array/object neutral. For example, each key can either be a direct field of a JSON object or a field of a JSON object within an array. * `EXISTS`: To get `EXISTS` behavior with shorthand syntax, use `JSON_MATCH_ANY` without a ``. ```sql JSON_MATCH_ANY(::?[, ]) ``` ## Arguments * `json`: the name of a JSON column or a JSON value. * `filter_path`: a path specification.  * Longhand syntax: a comma-separated list of dictionary keys or zero-indexed array positions. * Shorthand syntax: a period-separated list of dictionary keys or zero-indexed array positions. * `filter_predicate`: an optional predicate expression which is applied to every value matching the filter path. Consists of special built-in functions. ## Filter Path * The `filter_path`, which is a set of keys or zero-indexed array positions, is used to recursively extract a JSON value or an element of a JSON array from the `json` argument. * If no `filter_predicate` is provided, true is returned if the filter path exists. * The `filter_path` is evaluated recursively against the JSON in the `json` column using the following rules. * If the current element identified by the `filter_path` is an object, the next key in the `filter_path` is matched against the keys in the object.  * If the current element identified by the `filter_path` is an array, the next key in the `filter_path` is either matched as a zero-indexed position in the array or against the keys of any object that is an element of the array. If both a zero-indexed array match or a key match are possible, the zero-indexed array match will be used. * It is possible that there are several values in a `json` value which match the `filter_path`. In this case, the `filter_predicate` is applied on all values. The function returns true if the predicate evaluates to true on one of the values. ## Filter Predicate * The `filter_predicate` is an optional predicate expression which is applied to every value matching the `filter_path`. * Several special built-in functions are used for the `filter_predicate`. The built-in functions that can be used in the `filter_predicate`  include: * [JSON\_GET\_TYPE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-get-type.md) * [JSON\_EXTRACT\_\](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-extract-type.md) * `MATCH_PARAM__STRICT` functions, as described below. * `MATCH_PARAM_JSON()` is used for all valid JSON types including true, false, JSON objects, and JSON arrays. There is intentionally no STRICT here. * The `filter_predicate` is evaluated by running the specified built-in function on the value identified by the `filter_path`. If the value at the `filter_path` does not match the ``, the built-in evaluates as `SQL-NULL`. * `MATCH_PARAM__STRICT()` will evaluate as a `SQL-NULL` if there is not a match to `` as defined below: * `MATCH_PARAM_BIGINT_STRICT()` matches only numeric JSON values (integers/floating point). Rounds to the nearest integer. * `MATCH_PARAM_BOOL_STRICT()` matches only JSON booleans (true/false). * `MATCH_PARAM_DOUBLE_STRICT()` matches all numeric types (integers/floats). Unlike `JSON_EXTRACT_DOUBLE()`, it will not convert non-numeric types (booleans, strings, JSON objects, and JSON arrays) to double values. * `MATCH_PARAM_STRING_STRICT()` matches only JSON strings. Unlike `JSON_EXTRACT_STRING()` it will not convert non-string types (not enclosed by quotes). Behaves the same as `JSON_EXTRACT_STRING()` with regards to the `json_extract_string_collation` global variable. * `MATCH_ELEMENTS` when added to the `JSON_MATCH_ANY` function, matches only the array elements themselves, not the entire array. ## Return Type * Return type is `TINYINT`. ## Return Value * If a `filter_predicate` is provided, returns `1` if the predicate evaluates to true for any value found at the `filter_path`. * If a `filter_predicate` is not provided, returns `1` if the `filter_path` exists in the JSON. * Returns `0` otherwise. ## MATCH\_ELEMENTS The `MATCH_ELEMENTS` keyword reduces search time in `JSON_MATCH_ANY()`. Without it, if an array is present at the end of the `filter_path`, the `filter_predicate` is first tested against the entire array, and then, if that test fails, the `filter_predicate` is tested against each individual element of the array. With `MATCH_ELEMENTS`, the `filter_predicate` is tested only against the array elements themselves. For example, given the following JSON document and the following `JSON_MATCH_ANY` query: ```json {“names”: [“Alex”, “Brad”, “Charlie”]} ``` ```sql JSON_MATCH_ANY(json_col::?names,MATCH_PARAM_STRING_STRICT() = “Brad”) ``` Without `MATCH_ELEMENTS`, the filter\_predicate, `MATCH_PARAM_STRING_STRICT() = “Brad”`, is first tested on the entire array, `[“Alex”, “Brad”, “Charlie”]`, on which it evaluates to false. Then, the `filter_predicate` is tested on each individual element of the array (first ”Alex”, then ”Brad”, and then ”Charlie”); the `filter_predicate` evaluates to true on ”Brad”. In certain cases, as in the above example, it does not make sense to test the `filter_predicate` on the entire array. The `MATCH_ELEMENTS` keyword allows this costly first step to be skipped. Rather than evaluating the `filter_predicate` on the entire JSON array as a whole, `MATCH_ELEMENTS` evaluates the predicate on each individual element of the array. To use `MATCH_ELEMENTS`, add the `MATCH_ELEMENTS` keyword after the `filter_path`. In the shorthand syntax, an asterisk (\*) is used for `MATCH_ELEMENTS`. `MATCH_ELEMENTS` is applied to the last key in the `filter_path` and requires that all matches using the last key are within an array. Adding `MATCH_ELEMENTS` to the query above, the new query is as follows. ```sql JSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() = "Brad", json_col, "names" MATCH_ELEMENTS) ``` The new query in shorthand syntax is as follows: ```sql JSON_MATCH_ANY(json_col::?names*, MATCH_PARAM_STRING_STRICT() = "Brad") ``` If the value at the end of the keypath is not an array, `MATCH_ELEMENTS` prevents  `JSON_MATCH_ANY()`  from testing the filter\_predicate. Thus, a JSON document like `{“names”: “Brad”}` evaluates as false using the above `JSON_MATCH_ANY()` query with `MATCH_ELEMENTS`. ## Examples The examples in this section use the table and values shown below: ```sql CREATE TABLE json_exp(id int, json_row JSON); INSERT INTO json_exp VALUES (1, '{"alpha": {"beta": 6}}'), (2, '{"alpha": {"beta": 4}}'), (3, '{"alpha": [{"beta": 3}, {"beta": 8}, {"gamma": 2}, {"beta": 6}]}'), (4, '{"alpha": {"beta": [1,2,3,4,5,6]}}'), (5, '{"alpha": 1, "beta": 1}'), (6, '{"alpha": {"beta": null}}'), (7, '{"alpha": {"beta": true}}'), (8, '{"alpha": {"beta": "6"}}'), (9, '{"alpha": ["zeta", "zeta", {"1": {"beta": 8}}]}'); ``` ```sql SELECT * FROM json_exp ORDER BY id; ``` ```output +---------------------------------------------------------------+ |id | json_row | +----+----------------------------------------------------------+ | 1 | {"alpha":{"beta":6}} | | 2 | {"alpha":{"beta":4}} | | 3 | {"alpha":[{"beta":3},{"beta":8},{"gamma":2},{"beta":6}]} | | 4 | {"alpha":{"beta":[1,2,3,4,5,6]}} | | 5 | {"alpha":1,"beta":1} | | 6 | {"alpha":{"beta":null}} | | 7 | {"alpha":{"beta":true}} | | 8 | {"alpha":{"beta":"6"}} | | 9 | {"alpha":["zeta","zeta",{"1":{"beta":8}}]} | +----+----------------------------------------------------------+ ``` ## Example 1 - Find Rows that Contain a Path Using JSON\_MATCH\_ANY\_EXISTS Use the `JSON_MATCH_ANY_EXISTS()` function to find rows that contain the path `alpha.beta` ('`alpha,'beta'` in longhand). The `ORDER BY` clause and is included to ensure consistent results. *Longhand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY_EXISTS(json_row, 'alpha', 'beta') ORDER BY id; ``` *Shorthand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.beta) ORDER BY id; ``` Both versions of the query produce the following result. ```output +---------------------------------------------------------------+ |id | json_row | +----+----------------------------------------------------------+ | 1 | {"alpha":{"beta":6}} | | 2 | {"alpha":{"beta":4}} | | 3 | {"alpha":[{"beta":3},{"beta":8},{"gamma":2},{"beta":6}]} | | 4 | {"alpha":{"beta":[1,2,3,4,5,6]}} | | 6 | {"alpha":{"beta":null}} | | 7 | {"alpha":{"beta":true}} | | 8 | {"alpha":{"beta":"6"}} | +----+----------------------------------------------------------+ ``` The `JSON_MATCH_ANY_EXISTS` function returns true for the row with id 3,  `{"alpha”: [{"beta”: 3}, {"beta”: 1}, {“gamma: 2}, {"beta”: 6}]}` , because `beta` is matched to a key of an object that is an element in the array. Thus the row with id 3 is included in the result. The row with id 6, `{"alpha":{"beta":null}}`, is included in the result because the path `alpha.beta` (`'alpha','beta'` in longhand) exists in the JSON in that row, the fact that the value at that path is null does not prevent the row from being included in the result. ## Example 2 - Search for a Value at a Path Using MATCH\_PARAM\_DOUBLE\_STRICT Consider the following query that uses `MATCH_PARAM_DOUBLE_STRICT()` to looks for the value `6` at the path `alpha.beta` (`'alpha','beta'` in longhand). *Longhand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(MATCH_PARAM_DOUBLE_STRICT() = 6, json_row, 'alpha', 'beta') ORDER BY id; ``` *Shorthand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.beta, MATCH_PARAM_DOUBLE_STRICT() = 6) ORDER BY id; ``` Both versions of the query produce the following result. ```output +---------------------------------------------------------------+ |id | json_row | +----+----------------------------------------------------------+ | 1 | {"alpha":{"beta":6}} | | 3 | {"alpha":[{"beta":3},{"beta":8},{"gamma":2},{"beta":6}]} | | 4 | {"alpha":{"beta":[1,2,3,4,5,6]}} | +----+----------------------------------------------------------+ ``` For the row with id 3,  `{"alpha”: [{"beta”: 3}, {"beta”: 1}, {“gamma”: 2}, {"beta”: 6}]}`, the `filter_path` matches to the objects with values `3`, `1`, and `6` and the `filter_predicate` is evaluated on those values. The value `6` evaluates to true, and the row is included in the result. For the row with id 4, `{"alpha”: {"beta”: [1,2,3,4,5,6]}}`, the `filter_predicate` is evaluated on all values in the array. The value `6` evaluates to true, and the row is included in the result. For the row with id 8, `{"alpha”: {"beta”: "6"}}`, the `filter_predicate` is evaluated on the JSON string value `"6"`, which causes `MATCH_PARAM_DOUBLE_STRICT()` to evaluate to `SQL-NULL` (distinct from JSON null) because of mismatching types. Thus, the filter predicate evaluates to false and this row is not included in the result. This is distinctly different from how `JSON_EXTRACT_DOUBLE()` works. ## Example 3 - Identify Null Values at a Path Using MATCH\_PARAM\_JSON Use the `MATCH_PARAM_JSON()` function to identify null values at the path `alpha.beta` (`'alpha','beta'` in longhand). The function `MATCH_PARAM_JSON()` extracts null as JSON null. *Longhand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(MATCH_PARAM_JSON() = 'null', json_row, 'alpha', 'beta'); ``` *Shorthand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.beta, MATCH_PARAM_JSON() = 'null'); ``` Both versions of the query produce the following result. ```output +---------------------------------------------------------------+ |id | json_row | +----+----------------------------------------------------------+ | 6 | {"alpha":{"beta":null}} | +----+----------------------------------------------------------+ ``` ## Example 4 - Extract Numeric Values at a Path Using MATCH\_PARAM\_BIGINT\_STRICT Use the `MATCH_PARAM_BIGINT_STRICT()` function to extract numeric values at the path `alpha.1.beta` (`'alpha','1', 'beta'`) that round to `1`. *Longhand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 8, json_row, 'alpha', 1, 'beta'); ``` *Shorthand Syntax:* ```sql SELECT id, json_row FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.`1`.beta, MATCH_PARAM_BIGINT_STRICT() = 8); ``` Both versions of the query produce the following result. ```output +---------------------------------------------------------------+ |id | json_row | +----+----------------------------------------------------------+ | 3 | {"alpha":[{"beta":3},{"beta":8},{"gamma":2},{"beta":6}]} | +----+----------------------------------------------------------+ ``` If a key in the `filter_path` can either index the array or match a key in an object in the array,  `JSON_MATCH_ANY()` will match the key as a zero-indexed array position. Therefore, the row with id 9, `{"alpha": ["zeta", "zeta", {"1": {"beta": 8}}]}`, does not match because the key `1` in the `filter_path` ( `alpha.1.beta`) can index the array but the value at index `1` is `"zeta"`, which is not equal to `8` and fails the `filter_predicate` test. ## Example 5 - Test a Predicate Against All Array Elements Using MATCH ELEMENTS Use the `MATCH_ELEMENTS` keyword to test a predicate directly against all elements in an array without first testing against the whole array. Create a `purchase_info` table with a column, `purchases` of type JSON, and insert the following data into this table. ```sql CREATE TABLE purchase_info(p JSON); INSERT INTO purchase_info VALUES ('{"purchase": {"items": [1,2,3], "name": "John"}, "order_id": 101}'), ('{"purchase": {"items": 1, "name": "Dave"}, "order_id": 102}'), ('{"purchase": {"items": [1], "name": "Ann"}, "order_id": 103}'), ('{"purchase": {"items": [2,3,4], "name": "Brian"}, "order_id": 104}'); ``` The first of the following two queries looks for purchases where item `1` was ordered. This query looks for a match for the value `1` either in the array of items (order 101) or in the value of items (order 102). However, in many cases, the desired behavior is to search only the elements of the array. The second query adds `MATCH_ELEMENTS` after the `purchase.items` `filter_path` to specify that only the elements of the array should be searched. ```sql -- without MATCH_ELEMENTS SELECT p::%order_id AS order_id, JSON_MATCH_ANY(p::?purchase.items, MATCH_PARAM_BIGINT_STRICT() = 1) AS ordered_item_one FROM purchase_info ORDER BY order_id; -- with MATCH_ELEMENTS SELECT p::%order_id AS order_id, JSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 1, p, "purchase", "items" MATCH_ELEMENTS) AS ordered_item_one_in_array FROM purchase_info ORDER BY order_id; ``` The result of the first `SELECT` query (without `MATCH_ELEMENTS`) is shown below. Orders 101, 102, and 103 match the `filter_predicate` because `1` is in the value of items in order 102 and in the array of items in order 101 and 103. This query demonstrates how the entire value of items, plus the individual values in the items array are searched. ```sql +----------+------------------+ | order_id | ordered_item_one | +----------+------------------+ | 101 | 1 | | 102 | 1 | | 103 | 1 | | 104 | 0 | +----------+------------------+ ``` The result of the second `SELECT` query (with `MATCH_ELEMENTS`) follows. Only orders 101 and 103 match the `filter_predicate` and the query is more efficient as the system skips matching the value of the items array itself. ```sql +----------+---------------------------+ | order_id | ordered_item_one_in_array | +----------+---------------------------+ | 101 | 1 | | 102 | 0 | | 103 | 1 | | 104 | 0 | +----------+---------------------------+ ``` The second query expressed with shorthand syntax is as follows: ```sql SELECT p::%order_id AS order_id, JSON_MATCH_ANY(p::?purchase.items*, MATCH_PARAM_BIGINT_STRICT() = 1) AS ordered_item_one_in_array FROM purchase_info; ``` ## Example 6 - Match Multiple Paths using JSON\_MATCH\_ANY With `JSON_MATCH_ANY`, it is possible to match multiple paths in a single JSON value. Create a `restaurants` table with a column of type JSON which will contain information about restaurants. Insert two rows into that table. Each row added to the table is a JSON array and each row represents information about restaurants for a particular city. ```sql CREATE TABLE restaurants_json ( restaurantinfo JSON ); INSERT INTO restaurants_json VALUES ('[{"city":"Paris", "type":"Bakery", "name":"La Boulangerie"}, {"city":"Paris", "type":"Pizzeria", "name":"Pizza Pizza"}]'), ('[{"city":"Rome", "type":"Bakery", "name":"La Panetteria"}, {"city":"Rome", "type":"Pizzeria", "name":"Roma Pizza"}]'); ``` It is possible to query the JSON arrays in the `restaurantinfo` column to match on both the `type` and `name` of the restaurant. The query below searches for restaurant information for cities that have a bakery named `La Boulangerie`. The row with restaurants in Paris matches as there is a bakery named `La Boulangerie` in Paris. ```sql SELECT * FROM restaurants_json WHERE JSON_MATCH_ANY( JSON_EXTRACT_STRING(MATCH_PARAM_JSON(),'type') = 'Bakery' AND JSON_EXTRACT_STRING(MATCH_PARAM_JSON(),'name') = 'La Boulangerie', restaurantinfo MATCH_ELEMENTS); ``` ```output +------------------------------------------------------------------------------------------------------------------+ | restaurantinfo | +------------------------------------------------------------------------------------------------------------------+ | [{"city":"Paris","name":"La Boulangerie","type":"Bakery"},{"city":"Paris","name":"Pizza Pizza","type":"Pizzeria"}] | +------------------------------------------------------------------------------------------------------------------+ ``` The query below looks for restaurant information for cities that have a bakery named `Pizza Pizza`. There are no matches and no results are returned. ```sql SELECT * FROM restaurants_json WHERE JSON_MATCH_ANY( JSON_EXTRACT_STRING(MATCH_PARAM_JSON(), 'type') = 'Bakery' AND JSON_EXTRACT_STRING(MATCH_PARAM_JSON(), 'name') = 'Pizza Pizza', restaurantinfo MATCH_ELEMENTS); ``` ```output Empty set (0.02 sec) ``` A query, such as the above two queries, which use a predicate (`WHERE` clause) that contains `JSON_MATCH_ANY()` with a nested `JSON_EXTRACT_()` over `MATCH_PARAM_JSON()` will result in a more efficient query. In this type of query only the columnstore segments for the keys `type` and `name` are decoded, which reduces the amount of data that is read and processed. ## Columnstore Optimization `JSON_MATCH_ANY()` is only optimized over columnstore if \ only contains: * `MATCH_PARAM_()` functions. * Constant literals: strings, integers, floating point numbers, booleans, and NULL. * Comparison operators: =, !=, <, <=, >, >=, <=>, IS, IN, and BETWEEN. * Logical operators: AND, OR, and NOT. * Typecasts: implicit and explicit. *** Modified at: August 27, 2025 Source: [/db/v9.1/reference/sql-reference/json-functions/json-match-any/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-match-any/) (An index of the documentation is available at /llms.txt)