Convert SQL to JSON

Why Convert SQL to JSON?

Converting SQL to JSON bridges the gap between relational databases and modern web technologies. JSON is the universal data interchange format for REST APIs, web applications, and NoSQL databases. By transforming SQL schemas and data into JSON, you create portable, language-agnostic data structures that can be consumed by any modern application, from frontend JavaScript frameworks to mobile apps and microservices.

One of the most common use cases for SQL-to-JSON conversion is migrating data from relational databases to NoSQL systems like MongoDB, CouchDB, or DynamoDB. SQL table rows naturally map to JSON objects, with column names becoming keys and cell values becoming JSON values. This conversion preserves data types (integers, decimals, booleans) and allows hierarchical restructuring through nested objects and arrays that leverage NoSQL document models.

For API development, converting SQL schema definitions to JSON creates documentation and contract specifications for web services. The JSON representation of a database schema can serve as the basis for API response schemas, Swagger/OpenAPI definitions, and JSON Schema validation rules. This alignment between database structure and API contracts ensures consistency across the data pipeline.

Modern databases increasingly support JSON natively. PostgreSQL has JSONB columns, MySQL has JSON data type, and SQL Server supports JSON functions. Converting SQL files to JSON facilitates working with these hybrid approaches, where relational schemas coexist with JSON document storage. The JSON output can also be used for seeding test databases, creating mock data for frontend development, and generating sample API responses.

Key Benefits of Converting SQL to JSON:

• API Integration: Create JSON data structures for REST API endpoints
• NoSQL Migration: Prepare data for MongoDB, CouchDB, DynamoDB
• Web Compatibility: Native format for JavaScript and web applications
• Schema Documentation: JSON representation of database structures
• Data Portability: Universal format readable by all programming languages
• Hierarchical Data: Restructure flat SQL data into nested JSON objects
• Testing and Mocking: Generate test fixtures and mock data from SQL

Practical Examples

Example 1: Table Data to JSON Array

Input SQL file (data.sql):

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'Alice Johnson', 'Engineering', 95000.00),
(2, 'Bob Smith', 'Marketing', 72000.00),
(3, 'Carol Davis', 'Engineering', 88000.00);

Output JSON file (data.json):

{
  "employees": [
    {
      "id": 1,
      "name": "Alice Johnson",
      "department": "Engineering",
      "salary": 95000.00
    },
    {
      "id": 2,
      "name": "Bob Smith",
      "department": "Marketing",
      "salary": 72000.00
    },
    {
      "id": 3,
      "name": "Carol Davis",
      "department": "Engineering",
      "salary": 88000.00
    }
  ]
}

Example 2: Schema Definition to JSON Schema

Input SQL file (schema.sql):

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2) DEFAULT 0.00,
    status ENUM('pending', 'shipped', 'delivered'),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Output JSON file (schema.json):

{
  "table": "orders",
  "columns": {
    "order_id": {
      "type": "integer",
      "primary_key": true,
      "auto_increment": true
    },
    "customer_id": {
      "type": "integer",
      "nullable": false,
      "foreign_key": {
        "table": "customers",
        "column": "id"
      }
    },
    "order_date": {"type": "date", "nullable": false},
    "total": {"type": "decimal", "precision": [10,2], "default": 0.00},
    "status": {"type": "enum", "values": ["pending","shipped","delivered"]}
  }
}

Example 3: Relational Data to Nested JSON

Input SQL file (relational.sql):

SELECT u.id, u.name, u.email,
       o.order_id, o.total, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.customer_id
WHERE u.id = 1;

-- Result: user with multiple orders

Output JSON file (relational.json):

{
  "user": {
    "id": 1,
    "name": "John Doe",
    "email": "[email protected]",
    "orders": [
      {
        "order_id": 101,
        "total": 149.99,
        "order_date": "2024-01-15"
      },
      {
        "order_id": 105,
        "total": 89.50,
        "order_date": "2024-02-20"
      }
    ]
  }
}

Frequently Asked Questions (FAQ)

Q: How are SQL data types mapped to JSON types?

A: SQL INT and BIGINT map to JSON numbers, VARCHAR/TEXT to JSON strings, BOOLEAN to JSON true/false, DECIMAL/FLOAT to JSON numbers, DATE/TIMESTAMP to JSON strings (ISO 8601), NULL to JSON null, and ENUM to JSON strings. BLOB/BINARY types are typically base64-encoded as JSON strings.

Q: Can I use the JSON output with MongoDB?

A: Yes, the JSON output is compatible with MongoDB's mongoimport tool and can be directly inserted into MongoDB collections. SQL table rows become JSON documents, and the converter can restructure flat relational data into nested documents that take advantage of MongoDB's document model.

Q: How are SQL relationships represented in JSON?

A: Foreign key relationships can be represented in multiple ways in JSON: as nested objects (embedding related data), as reference IDs (keeping the relational structure), or as both. The converter preserves foreign key references and can optionally denormalize data into nested JSON structures.

Q: Is the JSON output valid and properly formatted?

A: Yes, the output is valid JSON conforming to RFC 8259. It uses proper UTF-8 encoding, correct escaping of special characters, and consistent indentation for readability. The output can be validated with any JSON validator or parsed by any JSON library.

Q: How are SQL queries (SELECT statements) converted?

A: SELECT statements are converted to JSON objects describing the query structure: selected columns, table references, join conditions, WHERE clauses, and aggregations. This creates a machine-readable representation of the query that can be used for documentation or query building tools.

Q: Can I convert large SQL dump files to JSON?

A: Yes, the converter handles SQL files of various sizes. For very large dumps with millions of INSERT statements, the converter streams data to avoid memory issues. The resulting JSON may be split into multiple files or use JSON Lines format (one JSON object per line) for large datasets.

Q: What happens to SQL stored procedures in JSON?

A: Stored procedures are converted to JSON objects containing the procedure name, parameters (with types and direction), and the SQL body as a string. This creates a structured representation useful for API documentation, code generation, and database schema management tools.

Q: How does SQL to JSON conversion help with API development?

A: The JSON representation of SQL schemas can be used to generate API response types, create Swagger/OpenAPI definitions, build GraphQL schemas, and create JSON Schema validation rules. This ensures consistency between your database structure and API contracts, reducing errors and speeding up development.