Collections:
JSON_CONTAINS() - Finding JSON in JSON
How to verify if a JSON value is contained in another a JSON (JavaScript Object Notation) value using the JSON_CONTAINS() function?
✍: FYIcenter.com
JSON_CONTAINS(target, candidate, path) is a MySQL built-in function that verifies if the given candidate JSON value is contained at the given location in the target JSON value.
JSON_CONTAINS() uses the following rules to calculate the return value:
For example:
SET @j = '{"a":true, "b":[1,[2,[3,4]]], "c":{"x":6,"y":[7,[8,9]]}}'; SELECT JSON_CONTAINS(@j, 'true'), JSON_CONTAINS(@j, 'true', '$.a'); -- +---------------------------+----------------------------------+ -- | JSON_CONTAINS(@j, 'true') | JSON_CONTAINS(@j, 'true', '$.a') | -- +---------------------------+----------------------------------+ -- | 0 | 1 | -- +---------------------------+----------------------------------+ SELECT JSON_CONTAINS(@j, '1', '$.b'), JSON_CONTAINS(@j, '7', '$.c'); -- +-------------------------------+-------------------------------+ -- | JSON_CONTAINS(@j, '1', '$.b') | JSON_CONTAINS(@j, '7', '$.c') | -- +-------------------------------+-------------------------------+ -- | 1 | 0 | -- +-------------------------------+-------------------------------+ SELECT JSON_CONTAINS(@j, '[3,4]'), JSON_CONTAINS(@j, '[3,4]', '$.b[1][1]'); -- +----------------------------+-----------------------------------------+ -- | JSON_CONTAINS(@j, '[3,4]') | JSON_CONTAINS(@j, '[3,4]', '$.b[1][1]') | -- +----------------------------+-----------------------------------------+ -- | 0 | 1 | -- +----------------------------+-----------------------------------------+ SELECT JSON_CONTAINS(@j, '{"a":true}'), JSON_CONTAINS(@j, '{"a":false}'); -- +---------------------------------+----------------------------------+ -- | JSON_CONTAINS(@j, '{"a":true}') | JSON_CONTAINS(@j, '{"a":false}') | -- +---------------------------------+----------------------------------+ -- | 1 | 0 | -- +---------------------------------+----------------------------------+
Note that rules described above treat nested arrays in a target JSON value as a single expanded array. For example:
SET @j = '{"a":true, "b":[1,[2,[3,4]]], "c":{"x":6,"y":[7,[8,9]]}}'; SELECT JSON_CONTAINS(@j, '[1,4]', '$.b'); -- +-----------------------------------+ -- | JSON_CONTAINS(@j, '[1,4]', '$.b') | -- +-----------------------------------+ -- | 1 | -- +-----------------------------------+ SELECT JSON_CONTAINS(@j, '{"y":9}', '$.c'); -- +-------------------------------------+ -- | JSON_CONTAINS(@j, '{"y":9}', '$.c') | -- +-------------------------------------+ -- | 1 | -- +-------------------------------------+
Reference information of the JSON_CONTAINS() function:
JSON_CONTAINS(target, candidate, path): int Verifies if the given candidate JSON value is contained at the given location in the target JSON value. Arguments, return value and availability: target: Required. The JSON value to be searched in. candidate: Required. The JSON value to search for. path: Optional. The default is "$". The location of in the target to be searched in. int: Return value. The verification result. Available since MySQL 5.7.
⇒ JSON_CONTAINS_PATH() - Finding Path in JSON
⇐ JSON_ARRAY_INSERT() - Inserting Value to JSON Array
2023-12-11, 248🔥, 0💬
Popular Posts:
How To Convert Numeric Expression Data Types using the CONVERT() Function in SQL Server Transact-SQL...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...