JSON to SQL変換 - CREATE TABLE＆INSERT文生成 オンライン

// JSON to SQL converter
function jsonToSql(jsonStr, options = {}) {
  const {
    tableName = 'my_table',
    dialect = 'mysql',
    includeCreate = true,
    includeInsert = true,
  } = options;

  const data = JSON.parse(jsonStr);
  const rows = Array.isArray(data) ? data : [data];
  if (rows.length === 0) return '';

  // Collect all column names
  const columns = [...new Set(rows.flatMap(Object.keys))];

  // Infer types from all rows
  const types = {};
  for (const col of columns) {
    for (const row of rows) {
      const val = row[col];
      if (val === null || val === undefined) continue;
      if (typeof val === 'boolean') types[col] = 'BOOLEAN';
      else if (typeof val === 'number') {
        types[col] = Number.isInteger(val) ? 'INT' : 'FLOAT';
      } else if (typeof val === 'object') types[col] = 'JSON';
      else types[col] = 'VARCHAR(255)';
    }
    if (!types[col]) types[col] = 'VARCHAR(255)';
  }

  const quote = dialect === 'mysql' ? (n) => `\`${n}\``
    : dialect === 'sqlserver' ? (n) => `[${n}]`
    : (n) => `"${n}"`;

  const parts = [];

  if (includeCreate) {
    const colDefs = columns.map(c => `  ${quote(c)} ${types[c]}`);
    parts.push(`CREATE TABLE ${quote(tableName)} (\n${colDefs.join(',\n')}\n);`);
  }

  if (includeInsert) {
    for (const row of rows) {
      const vals = columns.map(c => {
        const v = row[c];
        if (v === null || v === undefined) return 'NULL';
        if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE';
        if (typeof v === 'number') return String(v);
        if (typeof v === 'object') return `'${JSON.stringify(v).replace(/'/g, "''")}'`;
        return `'${String(v).replace(/'/g, "''")}'`;
      });
      parts.push(
        `INSERT INTO ${quote(tableName)} (${columns.map(quote).join(', ')}) VALUES (${vals.join(', ')});`
      );
    }
  }

  return parts.join('\n\n');
}

// Example usage
const json = '[{"id":1,"name":"Alice","active":true},{"id":2,"name":"Bob","active":false}]';
console.log(jsonToSql(json, { tableName: 'users', dialect: 'postgresql' }));