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.
Here is some 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:
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.
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;
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
B.Tech, M.B.A
Founder & CEO, Lead Full-Stack .Net developer
ALight Technology And Services Limited
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.
