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, 190🔥, 0💬
Popular Posts:
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...