Output MySQL in JSON Format

I was recently asked if there is anyway to select non-JSON data from a MySQL database and output that data in JSON format. There are two very easy ways to to this.

JSON Array

If you need a JSON array, it is very easy to use the JSON_ARRAY() function. Simply select the columns you want as the argument to JSON_ARRAY()

SQL> select
json_array(continent,Population, Code)
from country
limit 5;
| json_array(continent,Population, Code) |
| ["North America", 103000, "ABW"] |
| ["Asia", 22720000, "AFG"] |
| ["Africa", 12878000, "AGO"] |
| ["North America", 8000, "AIA"] |
| ["Europe", 3401200, "ALB"] |
5 rows in set (0.0006 sec)


However to get a JSON object you need to do a little more work. JSON objects require pairs – key/value pairs — so you need to pass a key, even a fictitious one for each value you desire. In the example below I use strings with the name of the column to pair up with the values

json_object('Continent', continent,
'Population', Population,
'Code', Code)
from country
limit 5;
| json_object('Continent', continent, 'Population', Population, 'Code', Code) |
| {"Code": "ABW", "Continent": "North America", "Population": 103000} |
| {"Code": "AFG", "Continent": "Asia", "Population": 22720000} |
| {"Code": "AGO", "Continent": "Africa", "Population": 12878000} |
| {"Code": "AIA", "Continent": "North America", "Population": 8000} |
| {"Code": "ALB", "Continent": "Europe", "Population": 3401200} |
5 rows in set (0.0007 sec)

Remember if you have questions about this or other MySQL JSON data type programming, please refer to my book MySQL and JSON A Practical Programming Guide -- David Stokes


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: