How to export a MySQL database to JSON

Question

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

User · Answer

Another possibility is using the MySQL Workbench.

There is a JSON export option at the object browser context menu and at the result grid menu.

More information on MySQL documentation: Data export and import.

User · Answer

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    That s because mysql restricts the length of the data returned by group concat to the value set in   group concat max len  as soon as it gets to the that amount it truncates and returns what it s gotten so far   You can set   group concat max len in a few different ways   reference The mysql documentation

User · Answer

THis is somthing that should be done in the application layer   For example  in php it is a s simple as  Edit Added the db connection stuff  No external anything needed    sql    select        db   new PDO    mysql  dbname    user   password     stmt    db- gt prepare  sql    stmt- gt execute     result    stmt- gt fetchAll     file put contents  output txt   json encode  result

User · Answer

HeidiSQL allows you to do this as well   Highlight any data in the DATA tab  or in the query result set    then right click and select Export Grid Rows option   This option then allows you can export any of your data as JSON  straight into clipboard or directly to file

User · Answer

Another solution  if you are using Ruby  is to write a connection script to the database with ActiveRecord  You will need to install it first  gem install activerecord    ruby   export-mysql rb require  rubygems  require  active record   ActiveRecord  Base establish connection     adapter   gt   mysql      database   gt   database name      username   gt   root      password   gt         host   gt   localhost     class Event  lt  ActiveRecord  Base  end class Person  lt  ActiveRecord  Base  end  File open  events json    w      f  f write Event all to json   File open  people json    w      f  f write Person all to json     You can also add methods to the ActiveRecord classes if you want to manipulate data first or  include or exclude certain columns   Person all to json  only   gt     id   name      With ActiveRecord you are not limited to JSON  You can just as easily export as XML or YAML  Person all to xml Person all to yaml   You are not limited to MySQL  Any database supported by ActiveRecord  Postgres  SQLite3  Oracle    etc     And it s worth mentioning you could open another handle to a database  require  active record   ActiveRecord  Base configurations  mysql          adapter    gt   mysql      database   gt   database name      username   gt   root      password   gt         host       gt   localhost      ActiveRecord  Base configurations  sqlite3          adapter    gt   sqlite3      database   gt   db development sqlite3     class PersonMySQL  lt  ActiveRecord  Base   establish connection  mysql  end  class PersonSQLite  lt  ActiveRecord  Base   establish connection  sqlite3  end   PersonMySQL all each do  person      PersonSQLite create person attributes except  id    end   Here is a quick little blog post about it http   www seanbehan com how-to-export-a-mysql-database-to-json-csv-and-xml-with-ruby-and-the-activerecord-gem

User · Answer

Using MySQL Shell you can out put directly to JSON using only terminal  echo  Your SQL query    mysqlsh --sql --result-format json --uri  username  localhost  schema name

User · Answer

It may be asking too much of MySQL to expect it to produce well formed json directly from a query   Instead  consider producing something more convenient  like CSV  using the INTO OUTFILE   path to output csv  FIELDS TERMINATED BY     snippet you already know  and then transforming the results into json in a language with built in support for it  like python or php   Edit python example  using the fine SQLAlchemy    class Student object          The model  a plain  ol python class        def   init   self  name  email  enrolled           self name   name         self email   email         self enrolled   enrolled      def   repr   self           return   lt Student  r   r  gt      self name  self email       def make dict self           return   name   self name   email   self email     import sqlalchemy metadata   sqlalchemy MetaData   students table   sqlalchemy Table  students   metadata          sqlalchemy Column  id   sqlalchemy Integer  primary key True           sqlalchemy Column  name   sqlalchemy String 100            sqlalchemy Column  email   sqlalchemy String 100            sqlalchemy Column  enrolled   sqlalchemy Date           connect the database   substitute the needed values  engine   sqlalchemy create engine  mysql   user pass host database      if needed  create the table  metadata create all engine     map the model to the table import sqlalchemy orm sqlalchemy orm mapper Student  students table     now you can issue queries against the database using the mapping  non students   engine query Student  filter by enrolled None     and lets make some json out of it  import json non students dicts     student make dict   for student in non students  students json   json dumps non students dicts

User · Answer

The simplest solution I found was combination of mysql and jq commands with JSON OBJECT query  Actually jq is not required if JSON Lines format is good enough  Dump from remote server to local file example  ssh remote server        quot mysql           --silent           --raw           --host  quot  quot  --port 3306           --user  quot  quot  --password  quot  quot            table           -e   quot SELECT JSON OBJECT  key   value  FROM table  quot        jq --slurp --ascii-output   quot     gt  dump json  books table example  ---- -------    id   book      ---- -------    1    book1      2    book2      3    book3     ---- -------   Query would looks like  SELECT JSON OBJECT  id   id   book   book  FROM books   dump json output                  quot id quot    quot 1 quot            quot book quot    quot book1 quot                        quot id quot    quot 2 quot            quot book quot    quot book2 quot                        quot id quot    quot 3 quot            quot book quot    quot book3 quot

User · Answer

For anyone that wants to do this using Python  and be able to export all tables without predefinining field names etc  I wrote a short script for this the other day  hope someone finds it useful   from contextlib import closing from datetime import datetime import json import MySQLdb DB NAME    x  DB USER    y  DB PASS    z   def get tables cursor       cursor execute  SHOW tables       return  r 0  for r in cursor fetchall      def get rows as dicts cursor  table       cursor execute  select   from     format table       columns    d 0  for d in cursor description      return  dict zip columns  row   for row in cursor fetchall     def dump date thing       if isinstance thing  datetime           return thing isoformat       return str thing    with closing MySQLdb connect user DB USER  passwd DB PASS  db DB NAME   as conn  closing conn cursor    as cursor      dump          for table in get tables cursor           dump table    get rows as dicts cursor  table      print json dumps dump  default dump date  indent 2

User · Answer

You can export any SQL query into JSON directly from PHPMyAdmin

User · Answer

This might be a more niche answer but if you are on windows and  MYSQL Workbench you can just select the table you want and click Export Import in the Result grid  This will give you multiple format options including  json

User · Answer

Use the following ruby code  require  mysql2   client   Mysql2  Client new     host   gt   your host    enter code here     database   gt   your database      username   gt   your username       password   gt   your password   table sql    show tables  tables   client query table sql   as   gt   array   open   output json    a      f             tables each do  table          sql    select   from    table first            res   client query sql   as   gt   json          f puts res to a join          n      end

User · Answer

I know this is old  but for the sake of somebody looking for an answer      There s a JSON library for MYSQL that can be found here You need to have root access to your server and be comfortable installing plugins  it s simple    1  upload the lib mysqludf json so into the plugins directory of your mysql installation  2  run the lib mysqludf json sql file  it pretty much does all of the work for you  If you run into trouble just delete anything that starts with  DROP FUNCTION       3  encode your query in something like this   SELECT json array           group concat json object  name  email   FROM      WHERE       and it will return something like                 name    something         email    someone somewhere net                      name    someone         email    something someplace com

User · Answer

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   gt 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

User · Answer

Also  If you are exporting in application layer don t forget to limit  results  For example if you ve 10M rows  you should get results part by part

[sql] How to export a MySQL database to JSON?

Examples related to sql

Examples related to mysql

Examples related to json