MySql table into JSON format

问题:

I would like to convert like 100 tables from MySql to MongoDb
So I think that the easiest way will be to import the data as JSON
Any generic query to convert MySql data to JSON format without using PHP /ruby/Python?

SELECT CONCAT(CONCAT('{"CompanyId":', company_id),"}")  AS JSON FROM company;

回答1:

Try this:

SET @schema = 'test_db';
SET @table = 'test';

SELECT CONCAT(
  'SELECT CONCAT(TRIM(TRAILING ', QUOTE(','), ' FROM CONCAT(', QUOTE('{'), ',', 
  GROUP_CONCAT(QUOTE('"'), ',', QUOTE(COLUMN_NAME), ',', 
  QUOTE('"'), ',', QUOTE(':'), ',', QUOTE('"'), ',', COLUMN_NAME, ',', 
  QUOTE('"'),',', QUOTE(',')),
  ')), ''}'') FROM ', @table
)
INTO @qry FROM 
  (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table) t;

SELECT @qry;
PREPARE stmt FROM @qry;
EXECUTE stmt;

Above query generates table data in JSON format. Using the query you can prepare a stored procedure taking database and table names as input parameters and populate your data. If you want to exclude some of the columns in a table, just modify the query which selects data from INFORMATION_SCHEMA database like SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND COLUMN_NAME NOT IN (**columns to exclude**).

回答2:

I extended oardic's answer to create a mysql procedure to generate json from table name along with null check. Here key is set to value null instead of empty string.

create procedure createJSON (IN tabelSchema varchar(255), IN tableName varchar(255), IN whereCondition varchar(255), OUT outJson longtext)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS RowJson;
  SELECT CONCAT(
      'CREATE TEMPORARY TABLE RowJson AS SELECT CONCAT(TRIM(TRAILING ', QUOTE(','), ' FROM CONCAT(', QUOTE('{'), ',',
      GROUP_CONCAT(QUOTE('"'), ',', QUOTE(COLUMN_NAME), ',',
                   QUOTE('"'), ',', QUOTE(':'), ',', 'IF(ISNULL(', COLUMN_NAME, '), "null", CONCAT(',QUOTE('"'),",", COLUMN_NAME, ",", QUOTE('"'),')),',
                   QUOTE(',')),
      ')), ''}'') as json FROM ', tableName, ' WHERE ', whereCondition
  )
  INTO @qry FROM
    (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE TABLE_SCHEMA = tabelSchema AND TABLE_NAME = tableName) t;

  PREPARE stmt FROM @qry;
  EXECUTE stmt;

  SELECT json into outJson FROM RowJson;
END;