SQL to JSON Lines Converter

About SQL to JSON Lines Converter

Convert SQL database dumps (CREATE TABLE and INSERT statements) to JSON Lines (JSONL) format, also known as newline-delimited JSON. Each line contains a valid JSON object or array, making it perfect for streaming data processing, big data applications, and log files.

Key Features

• Line-Delimited JSON: One JSON object or array per line
• Automatic Type Detection: Converts numbers, booleans, and null values
• Streaming Compatible: Process data line-by-line without loading entire file
• Big Data Ready: Works with Hadoop, Spark, Elasticsearch, and MongoDB
• Appendable: Easy to append new records without parsing entire file
• Memory Efficient: Process large datasets with minimal memory usage
• File Download: Save as .jsonl file

How to Use

1. Input SQL Data: Paste your SQL CREATE TABLE and INSERT statements or upload a .sql file
2. Choose Format: Toggle between objects (with headers) or arrays per line
3. Copy or Download: Use the Copy or Download button to save your JSONL
4. Import to Big Data Tools: Use with Hadoop, Spark, Elasticsearch, or MongoDB

Output Formats

Objects per Line (with headers):

{"id":1,"name":"Laptop","price":999.99,"available":true}
{"id":2,"name":"Mouse","price":24.99,"available":true}
{"id":3,"name":"Keyboard","price":79.99,"available":true}

Arrays per Line (without headers):

["id","name","price","available"]
[1,"Laptop",999.99,true]
[2,"Mouse",24.99,true]
[3,"Keyboard",79.99,true]

Type Detection

• Numbers: Automatically converts numeric strings to numbers (123, 45.67)
• Booleans: Converts 'true' and 'false' strings to boolean values
• Null: Converts 'NULL' and empty strings to null
• Strings: All other values remain as strings

Example Conversion

SQL Input:

CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  available BOOLEAN
);

INSERT INTO products VALUES (1, 'Laptop', 999.99, true);
INSERT INTO products VALUES (2, 'Mouse', 24.99, false);

JSON Lines Output (Objects):

{"id":1,"name":"Laptop","price":999.99,"available":true}
{"id":2,"name":"Mouse","price":24.99,"available":false}

Common Use Cases

• Big Data Processing: Import into Hadoop, Spark, or Hive for distributed processing
• Elasticsearch: Bulk import data into Elasticsearch indices
• MongoDB: Import data using mongoimport with --jsonArray flag
• Log Files: Store structured logs in JSONL format
• Data Streaming: Process data streams line-by-line in real-time
• ETL Pipelines: Use as intermediate format in data transformation pipelines

Advantages of JSON Lines

• Streamable: Process one line at a time without loading entire file
• Appendable: Add new records by appending lines to the file
• Memory Efficient: Handle large datasets with minimal memory
• Simple Format: Easy to parse and generate
• Text-Based: Human-readable and compatible with text tools
• No Commas: No need for comma separators between records

Integration Examples

Node.js Streaming:

const fs = require('fs');
const readline = require('readline');

const rl = readline.createInterface({
  input: fs.createReadStream('output.jsonl')
});

rl.on('line', (line) => {
  const data = JSON.parse(line);
  console.log(data);
});

Python Processing:

import json

with open('output.jsonl', 'r') as f:
    for line in f:
        data = json.loads(line)
        print(data)

MongoDB Import:

mongoimport --db mydb --collection products --file output.jsonl

Elasticsearch Bulk Import:

curl -X POST "localhost:9200/_bulk" -H 'Content-Type: application/x-ndjson' --data-binary @output.jsonl

Big Data Tools Support

• Apache Hadoop: Use as input format for MapReduce jobs
• Apache Spark: Read with spark.read.json() for distributed processing
• Elasticsearch: Bulk API supports JSONL for efficient indexing
• MongoDB: mongoimport tool supports JSONL format
• Apache Kafka: Use as message format for streaming
• AWS Athena: Query JSONL files directly in S3

When to Use JSON Lines

Use JSON Lines when:

• Processing large datasets that don't fit in memory
• Streaming data in real-time
• Appending records to existing files
• Working with big data tools (Hadoop, Spark, etc.)
• Storing structured log files
• Building ETL pipelines

Use regular JSON when:

• Working with small datasets that fit in memory
• Building REST APIs
• Need nested data structures
• Human readability is priority

Supported SQL Syntax

• CREATE TABLE: Extracts column names for JSON object keys
• INSERT INTO: Parses data values from INSERT statements
• Data Types: Handles all SQL data types (VARCHAR, INT, DECIMAL, BOOLEAN, etc.)
• Quoted Strings: Handles single and double quotes with proper escaping
• NULL Values: Converts NULL to JSON null

File Format Specification

• Extension: .jsonl or .ndjson
• MIME Type: application/jsonlines or application/x-ndjson
• Line Separator: LF (\\n) or CRLF (\\r\\n)
• Encoding: UTF-8
• Each Line: Must be valid JSON (object or array)

Privacy & Security

All conversions happen locally in your browser. Your SQL data is never uploaded to any server, ensuring complete privacy and security.