Output MySQL in JSON Format

by

Open Source DBA's Blog

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)

JSON_OBJECT

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…

View original post 105 more words

Advertisements

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: