Collections:
JSON_OBJECT() - Creating JSON Object
How to generate a JSON (JavaScript Object Notation) object using the JSON_OBJECT() function?
✍: FYIcenter.com
JSON_OBJECT(key1, val1, key2, val2, ...) is a MySQL built-in function that converts a list of key-value pairs into a JSON object. Key arguments are converted into JSON object keys. Value arguments can be JSON scalars (String, Number, Boolean, or Null), JSON arrays, or JSON objects.
JSON scalars can be specified as literals of MySQL equivalent data types. For example:
SELECT JSON_OBJECT('id', 87, 'name', 'carrot', 'price', 0.99); -- +--------------------------------------------------------+ -- | JSON_OBJECT('id', 87, 'name', 'carrot', 'price', 0.99) | -- +--------------------------------------------------------+ -- | {"id": 87, "name": "carrot", "price": 0.99} | -- +--------------------------------------------------------+
However, JSON arrays and JSON objects need to be constructed with CAST(), JSON_ARRAY(), or JSON_OBJECT() functions. For example:
SELECT JSON_OBJECT('id', 87, 'primes', CAST('[3,5,7]' AS JSON)); -- +----------------------------------------------------------+ -- | JSON_OBJECT('id', 87, 'primes', CAST('[3,5,7]' AS JSON)) | -- +----------------------------------------------------------+ -- | {"id": 87, "primes": [3, 5, 7]} | -- +----------------------------------------------------------+ SELECT JSON_OBJECT('id', 87, 'primes', JSON_ARRAY(3,5,7)); -- +----------------------------------------------------+ -- | JSON_OBJECT('id', 87, 'primes', JSON_ARRAY(3,5,7)) | -- +----------------------------------------------------+ -- | {"id": 87, "primes": [3, 5, 7]} | -- +----------------------------------------------------+ SELECT JSON_OBJECT('id', 87, 'site', JSON_OBJECT('name', 'FYI')); -- +-----------------------------------------------------------+ -- | JSON_OBJECT('id', 87, 'site', JSON_OBJECT('name', 'FYI')) | -- +-----------------------------------------------------------+ -- | {"id": 87, "site": {"name": "FYI"}} | -- +-----------------------------------------------------------+
Reference information of the JSON_OBJECT() function:
JSON_OBJECT(key1, val1, key2, val2, ...): json Returns a JSON object converted from a given list of key-value pairs. Key arguments are converted into JSON object keys. Value arguments can be JSON scalars (String, Number, Boolean, or Null), JSON arrays, or JSON objects. Arguments, return value and availability: key1, val1, key2, val2, ...: One or more JSON key-value pairs to be converted. json: Return value. The converted JSON object. Available since MySQL 5.7.
⇒ JSON_OVERLAPS() - Checking JSON Overlaps
⇐ JSON_MERGE_PRESERVE() - Merging JSON with All Members
2023-12-10, 245🔥, 0💬
Popular Posts:
How To Generate CREATE TABLE Script on an Existing Table in SQL Server? If you want to know how an e...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...