Add free search for your website. Sign up now! https://webveta.alightservices.com/
Categories
MySQL SQL

Formatting the output of MySQL into JSON

Sometimes there would be a need to format the output of SQL statement into a format such as XML or JSON.

SQL Server has “FOR XML” clause. I have been familiar with SQL Server in the past. I had a similar use-case with MySQL. MySQL has functions for outputting as JSON.

This can be achieved by using JSON_OBJECT, JSON_ARRAYAGG functions.

Assume we have a table ‘Sample’ with columns Id, Name.

MySQL Table – Sample

Here is some sample data:

Sample data

We can use JSON_OBJECT to create JSON objects like this:

select json_object('id', id, 'name', name) from sample;

In the above line of code we are asking that for each row create a JSON document with the attribute names of ‘id’, ‘name’ and use the column values of id, name for values. The output looks like this:

JSON_OBJECT output

We can use JSON_ARRAYAGG for aggregating the values into a single result like this, and even works with grouping.

select json_arrayagg(id) from sample;

The following output would be obtained.

Output of JSON_ARRAYAGG

Combing these two functions to generate a single JSON document with array for each row of the output.

select json_arrayagg(json_object('id', id, 'name', name)) from sample;
Output of JSON_ARRAYAGG and JSON_OBJECT

This code snippet has been very helpful for me when I was doing something for my own I.T Startup – ALight Technology And Services Limited. I came across these functions while searching on Google in StackOverflow thread.

I am hoping this blog post helps someone.

Happy development.

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.