SELECT FOR JSON PATH - SQL Server to MySQL Migration

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.