WebVeta - Advanced, unified, consistent search for your website(s), from content of your website(s), blogs(s). First 50 customers, who sign-up prior to 15/05/2024 get unlimited access to existing features, newer features for at least 1 year. 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.

I don’t have any fake aliases, nor any virtual aliases like some of the the psycho spy R&AW traitors of India. NOT associated – “ass”, eass, female “es”, “eka”, “ok”, “okay”, “is”, “erra”, yerra, karan, kamalakar, diwakar, kareem, karan, sowmya, zinnabathuni, bojja srinivas (was a friend and batchmate 1998 – 2002), mukesh golla (was a friend and classmate 1998 – 2002), thota veera, uttam’s, bandhavi’s, bhattaru’s, thota’s, bojja’s, bhattaru’s or Arumilli srinivas or Arumilli uttam (may be they are part of a different Arumilli family – not my family).

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.