Collections:
JSON_ARRAYAGG() - Building JSON Array in Group
How to build a JSON array with a field expression in result set groups using the JSON_ARRAYAGG() function?
✍: FYIcenter.com
JSON_ARRAYAGG(exp) is a MySQL built-in aggregate function that creates a JSON array with a field expression in result set groups. For example:
SELECT help_category_id, LEFT(JSON_ARRAYAGG(name), 40) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+---------------------------------------------+ -- | help_category_id | LEFT(JSON_ARRAYAGG(name), 40) | -- +------------------+---------------------------------------------+ -- | 1 | ["HELP_DATE", "HELP_VERSION"] | -- | 2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO... | -- | 3 | ["HELP COMMAND", "SET", "SET CHARACTER S... | -- | 4 | ["TRUE", "FALSE"] | -- | 5 | ["GEOMETRY HIERARCHY", "SPATIAL COLUMNS"... | -- ... -- +------------------+---------------------------------------------+ SELECT help_category_id, help_topic_id, name FROM mysql.help_topic WHERE help_category_id = 5; -- +------------------+---------------+--------------------+ -- | help_category_id | help_topic_id | name | -- +------------------+---------------+--------------------+ -- | 5 | 40 | GEOMETRY HIERARCHY | -- | 5 | 43 | SPATIAL COLUMNS | -- | 5 | 44 | SPATIAL INDEXES | -- +------------------+---------------+--------------------+
JSON_ARRAYAGG() is also a window function, you can call it with the OVER clause to create a JSON array with the given expression in the current window. For example:
SELECT help_topic_id AS tid, help_category_id AS cid, LEFT((JSON_ARRAYAGG(name) OVER w), 40) FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +-----+-----+------------------------------------------+ -- | tid | cid | LEFT((JSON_ARRAYAGG(name) OVER w), 40) | -- +-----+-----+------------------------------------------+ -- | 0 | 1 | ["HELP_DATE", "HELP_VERSION"] | -- | 1 | 1 | ["HELP_DATE", "HELP_VERSION"] | -- | 2 | 2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO | -- | 6 | 2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO | -- | 7 | 2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO | -- | 8 | 2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO | -- | 9 | 2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO | -- ... -- +-----+-----+------------------------------------------+
Reference information of the JSON_ARRAYAGG() function:
JSON_ARRAYAGG(exp): json Creates a JSON array with a field expression in result set groups. Arguments, return value and availability: exp: Required. The field expression used as JSON array member. json: Return value. The JSON array encoded as a string. Available since MySQL 5.7.
⇒ JSON_OBJECTAGG() - Building JSON Object in Group
⇐ GROUPING() - Identifying Super-Aggregate Row
2023-12-10, 183🔥, 0💬
Popular Posts:
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL? If the underlying...