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.

Frequently Asked Questions (FAQ)

What is the difference between JSON and JSON Lines?

Standard JSON typically represents an entire dataset as a single JSON value (for example, an array of objects). JSON Lines stores one JSON value per line, which makes it easier to stream, append, and process data incrementally.

When should I use objects per line vs arrays per line?

Objects per line are best when you want self-describing records with field names, which is ideal for log processing and analytics. Arrays per line are more compact and can be useful when you already know the column order from another source.

Can I safely append new records to an existing JSONL file?

Yes. Because each line is an independent JSON value, you can append new lines to the end of the file without re-writing existing content. Just ensure that each appended line is valid JSON.

How are NULL and empty values represented?

Values that are NULL (or treated as empty) in SQL are converted to JSON null. This preserves missing-data semantics in downstream systems and big data tools.

Is the JSON Lines output compatible with big data systems?

Yes. The generated JSONL is suitable for tools like Hadoop, Spark, Elasticsearch, MongoDB, and many log processors that accept one JSON value per line.

Can I mix objects and arrays in the same JSONL file?

Technically you can, but it is not recommended. For best interoperability and simpler processing logic, keep each JSONL file consistent by using either all objects or all arrays per line.