Today I had to conveniently dump mysql data in JSON. Here is a script you can use to convert the output of a MYSQL SELECT command into data that can be used by JSON applications. Make sure you have a recent version of sed (I have 4.1.5). Create a file called mysql_to_json.sh fill it with the following lines:
#!/bin/sh
sed -e 's/\t/\",\"/g' \
-e 's/^/\[\"/' \
-e 's/$/\"\],/' \
-e '1s/\(.*\)/\{\"fields\":\1\ \"data\":[/g' -e '$s/.$/\]\}/' \
| tr -d "\n"
You can use this script to convert SELECTs into JSON - SELECT INTO OUTFILE using JSON, so to speak.
Say you have a MySQL table that looks like this:
mysql -e "SELECT * FROM foo"
+------+------+-------+
| one | two | three |
+------+------+-------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+------+------+-------+
2 rows in set (0.02 sec)
You can then print this (or any other) table in JSON using the following command:
mysql -e "SELECT * FROM foo" | ./mysql_to_json.sh
{"fields":["one","two","three"],"data":[["1","2","3"],["4","5","6"]]}
Obviously, you need to be careful that your table does not contain " as a value.
Since I came up with a sed script to do this, I am sure there is a way to do it way more easily and elegantly using the EXPORT_OPTIONS of MySQL or another trick. But I will leave that as well as optimization of the above script to the nerds. This works for me and has no funky dependencies.
