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.

spacedog · Accepted Answer · 2022-05-17 17:56:47Z

3

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.

answered May 17, 2022 at 17:56

spacedog

5861 gold badge8 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Impaler Over a year ago

You should note that the OP query returns multiple rows -- one for each JSON object -- while this solution returns a single row with all objects assembled into a single JSON array. Both queries are correct, depending on the result that is needed.

Collectives™ on Stack Overflow

MySQL not returning valid Json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related