In SQL Server, you can use SELECT FOR JSON PATH to generate JSON from the query result i.e. aggregate values from multiple rows into a single JSON array.
Consider a sample table:
CREATE TABLE colors (name VARCHAR(30), category CHAR(1)); -- Insert sample rows INSERT INTO colors VALUES ('Red', 'R'); INSERT INTO colors VALUES ('Blue', 'B'); INSERT INTO colors VALUES ('Green', 'G');
SQL Server:
-- Create JSON array SELECT name, category FROM colors FOR JSON PATH; # [{"name":"Red","category":"R"},{"name":"Blue","category":"B"},{"name":"Green","category":"G"}]
In MySQL you can use JSON_ARRAYAGG and JSON_OBJECT functions as follows:
MySQL:
-- Create JSON array SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'category', category)) FROM colors; # [{"name":"Red","category":"R"},{"name":"Blue","category":"B"},{"name":"Green","category":"G"}]
Note that names of JSON keys have to be specified in JSON_OBJECT function explicitly.
For more information, see SQL Server to MySQL Migration.