Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to export a MySQL database to JSON?

Tags:

json

sql

mysql

I am interested in exporting a subset of values from a MySQL database into a JSON-formatted file on disk.

I found a link that talks about a possible way to do this: http://www.thomasfrank.se/mysql_to_json.html

... but when I use the method from that page, it seems to work but with two problems:

1) It only returns around 15 results, with the last one abruptly cut off (incomplete). My standard query for this returns around 4000 results when I just run it as SELECT name, email FROM students WHERE enrolled IS NULL But when I run it as:

SELECT       CONCAT("[",           GROUP_CONCAT(                CONCAT("{name:'",name,"'"),                CONCAT(",email:'",email,"'}")           )      ,"]")  AS json FROM students WHERE enrolled IS NULL; 

... as described in the link, it only returns (as I mentioned) 15 results. (fwiw, I checked these results against the 4000 I'm supposed to get, and these 15 are the same as the first 15 of the 4000)

2) There seem to be "escape" characters included in the actual file when I add INTO OUTFILE '/path/to/jsonoutput.txt' FIELDS TERMINATED BY ',' to the end of that query. So commas end up looking like '\,' when obviously I would just like to have the commas without the \.

Any ideas on how to get proper JSON output from MySQL? (Either using this method, or some other method)?

Thanks!

like image 269
mindthief Avatar asked Feb 18 '11 00:02

mindthief


People also ask

Does MySQL have JSON?

MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.


1 Answers

If you have Ruby, you can install the mysql2xxxx gem (not the mysql2json gem, which is a different gem):

$ gem install mysql2xxxx 

and then run the command

$ mysql2json --user=root --password=password --database=database_name --execute "select * from mytable" >mytable.json 

The gem also provides mysql2csv and mysql2xml. It's not as fast as mysqldump, but also doesn't suffer from some of mysqldump's weirdnesses (like only being able to dump CSV from the same computer as the MySQL server itself)

like image 167
Seamus Abshere Avatar answered Oct 07 '22 23:10

Seamus Abshere