MySQL not returning valid Json

Question

Using MySQL Server, I have a simple SELECT statement and need to return Json. All I've found so far is the JSON_OBJECT syntax like this:

SELECT JSON_OBJECT( 
'Id', Id, 
'Name', Name, 
'State', State)
FROM ACustomer;

Which returns this:

{"Id": 1, "Name": "Ajax", "State": "WA"}
{"Id": 2, "Name": "Bluebell", "State": "TX"}
{"Id": 3, "Name": "Cornpops", "State": "CA"}

but it needs to return valid Json like this:

[
{"Id": 1, "Name": "Ajax", "State": "WA"},
{"Id": 2, "Name": "Bluebell", "State": "TX"},
{"Id": 3, "Name": "Cornpops", "State": "CA"}
]

In SQL Server we add this to the end of the select statement and everything is formatted perfectly:

FOR JSON PATH;

Is there a way to do this in MySQL?

Thank you.

user_15797317 · Answer

You need to include the JSON_ARRAYAGG syntax like this:

SELECT JSON_ARRAYAGG(JSON_OBJECT( 
'Id', Id, 
'Name', Name, 
'State', State))
FROM ACustomer;

I know, this is not documented well and you would think this would be much more common.

Hope this helps.