Creating JSON as a select result in a MySQL Query

January 12, 2009 in MySQL, php, Programming

So I had this crazy idea at work, I needed to get all the data out of an entire table in a single column. So I decided to return the table as a preformatted JSON array that I could decode straight into a php array for manipulation.

The Code:

Suppose a table format like so: (forgive my crude mysql client output attempt)

-----------------------
id (int) | val (int)
------------------------
- 1 | 2 -
- 3 | 4 -
------------------------


SELECT CONCAT('{',CAST(GROUP_CONCAT(CONCAT('"',table.key,'":',table.value)) AS CHAR),'}') AS alias FROM table GROUP BY table.id

Your result would be something like:
{"1":2,"3":4}

Now you can pump this straight into a json_decode function and manipulate it as you please.

Note:

• This is probably not the best method when returning more than a couple rows and your table you are building this array from doesn’t have a huge number or rows either.
• This doesn’t account for proper escaping to flow properly into json_decode (think string with quotes) but should be flawless if you table is only integer based.
• The data type of your keys here (1 & 3 in the example) will show up as string because of the quotes, if you want to drop the quotes they should come back as integers when converted in the json_decode
  • If you add quotes around the value (2 & 4) they will be treated as strings
  • If you had a simple table with numeric id & a alphanumeric value you would be safe to use this if you quoted the value
• Requires: PHP 5 >= 5.2.0, OR PECL json >= 1.2.0
• A new function is available in php 5.3.0 – json_last_error

Tags: MySQL, php