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, 240🔥, 0💬
Popular Posts:
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
Where to find Oracle database server tutorials? Here is a collection of tutorials, tips and FAQs for...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...