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, 251🔥, 0💬
Popular Posts:
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
How To Get a List of All Tables with "sys.tables" View in SQL Server? If you want to see the table y...
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...