Passion & Opportunity ? continue : break

How to convert MySQL query result to JSON?
Written: 2019-03-08 14:34:34 Last update: 2019-06-16 11:51:08

To convert MySQL query result directly to JSON object or array, we need to use MySQL functions such as concat(), concat_ws() and group_concat().

Before we see the demo below, we need to know the detail different between MySQL's concat() & concat_ws() and group_concat(), so then we could understand why we need to use them

Test data:

----------------------------------
studentId | firstName | lastName |
----------------------------------
1         | Mike      | Bush     |
2         | Joshua    | Bush     |
3         | Rudy      | Lynn     |
4         | George    | Mandy    |
----------------------------------
  • concat(): add two or more expressions together.
    Example 1:
    SELECT CONCAT(firstName, lastName)
    FROM students
    WHERE studentId = 2
    
    Output:
    JoshuaBush
    Example 2:
    SELECT CONCAT("Today", "is", "warm");
    Output:
    Todayiswarm
    NOTE: there is no separator, everything will be combined as-is.
  • concat_ws(): is the same as concat() but with extra parameter, the '_ws' stands for 'with separator' so we must define a parameter (only the first string/expression) as the separator.
    Example 1:
    SELECT CONCAT_WS(',', firstName, lastName) as student
    FROM students
    
    Output:
    --------------
    student      |
    --------------
    Mike,Bush    |
    Joshua,Bush  |
    Rudy,Lynn    |
    George,Mandy |
    --------------
    
    Example 2:
    SELECT CONCAT_WS(" - ", "Today", "is", "warm");
    Output:
    Today - is - warm
  • group_concat(): return only a row which is a string with concatenated NON-NULL value from a group.
    Example 1:
    SELECT GROUP_CONCAT(firstName, lastName)
    FROM (
      SELECT firstName, lastName
      FROM students
    ) t1
    
    Output:
    MikeBush,JoshuaBush,RudyLynn,GeorgeMandy
    NOTE: the default separator for each row is ',' but if we want to use different separator then we must define it with keyword 'separator'.
    Example 2:
    SELECT GROUP_CONCAT(firstName, ' ', lastName SEPARATOR ' & ')
    FROM (
      SELECT studentId, firstName, lastName
      FROM students
    ) t1
    
    Output:
    Mike Bush & Joshua Bush & Rudy Lynn & George Mandy

After we understood the difference between those 3 functions then we can start to convert MySQL query to JSON like the example below:

Example 1:
SELECT CONCAT('[[', result, ']]') as final
FROM (
  SELECT GROUP_CONCAT(CONCAT_WS(',', studentId, CONCAT('"', firstName, '"'), CONCAT('"', lastName, '"')) SEPARATOR '],[') as result
  FROM (
    SELECT studentId, firstName, lastName
    FROM students
  ) t1
) t2
Output:
[[1,"Mike","Bush"],[2,"Joshua","Bush"],[3,"Rudy","Lynn"],[4,"George","Mandy"]]
Prettified JSON:
[
  [
    1,
    "Mike",
    "Bush"
  ],
  [
    2,
    "Joshua",
    "Bush"
  ],
  [
    3,
    "Rudy",
    "Lynn"
  ],
  [
    4,
    "George",
    "Mandy"
  ]
]
The above simple query simply return a JSON array without any JSON keyname, if we want to display keyname then we can easily modify like example below.
Example 2:
SELECT CONCAT('[{', result, '}]') as final
FROM (
  SELECT GROUP_CONCAT(CONCAT_WS(',', CONCAT('"studentId": ', studentId), CONCAT('"firstName": "', firstName, '"'), CONCAT('"lastName": "', lastname, '"')) SEPARATOR '},{') as result
  FROM (
    SELECT studentId, firstName, lastName
    FROM students
  ) t1
) t2
Output:
[{"studentId": 1,"firstName": "Mike","lastName": Bush},{"studentId": 2,"firstName": "Joshua","lastName": Bush},{"studentId": 3,"firstName": "Rudy","lastName": Lynn},{"studentId": 4,"firstName": "George","lastName": Mandy}]
Prettified JSON:
[
  {
    "studentId": 1,
    "firstName": "Mike",
    "lastName": "Bush"
  },
  {
    "studentId": 2,
    "firstName": "Joshua",
    "lastName": "Bush"
  },
  {
    "studentId": 3,
    "firstName": "Rudy",
    "lastName": "Lynn"
  },
  {
    "studentId": 4,
    "firstName": "George",
    "lastName": "Mandy"
  }
]
NOTE:
  • All concat(), concat_ws() and group_concat() can be use on any modern MySQL version, I've checked MySQL 4.1 documentation and it support them all.
  • group_concat() will have default buffer length limit (depend on MySQL version, normally 1024 bytes), check current value of 'group_concat_max_len' by query:
    SHOW VARIABLES LIKE 'group%';
    if there are many records then the content will be cut silently (no error, must see MySQL warning log), to enlarge buffer size then we need to modify 'group_concat_max_len' using query:
    set session group_concat_max_len = [large value in bytes];
    In my experience, I was able to generate around 650 KB long JSON text, I used '4194304' (4MB), maybe too much for my need but working fine, please check your MySQL server setting and adjust accordingly. As far as I can tell there is no security issue only memory requirement will be larger.
  • Implementing direct conversion from MySQL query result to JSON is very important, because we don't need to do it in code like Java, PHP, etc. Using code to convert will takes more server resources, slower, human-error prone and difficult to maintain the code, especially if the query is very long, complex and the code is shared among multiple developers, so the possibility to be able to convert MySQL query to JSON is very important for some cases.


USE CASE?
  • I have some web APIs which will be called by many clients (web and app), I use MySQL trigger to catch an event which then generates a JSON string and save into 'generated-content-cache' table, so every time clients call the API then I will simply get the 'generated-content-cache' and return the JSON string as-is. The combination of MySQL trigger, convert MySQL query result to JSON and save to cache table logic have reduced a lot of server resources, increase speed, increase server stability and naturally allows more connections.