DCT Flattify - Flatten Nested JSON

Convert nested JSON structures to flat formats or SQL SELECT statements.

When to Use

Use this skill when you need to:

• Convert nested API responses to flat format
• Transform hierarchical JSON for tabular analysis
• Generate SQL to query nested JSON files
• Unnest deeply nested structures
• Prepare JSON data for CSV export

Installation

which dct || go build -o dct && chmod +x ./dct

Usage

dct flattify <json> [flags]

Arguments

• json: JSON file path or inline JSON string

Flags

• -s, --sql: Generate DuckDB SQL SELECT statement
• -o, --output <file>: Output to file instead of stdout

Examples

Flatten JSON File

Basic flattening:

dct flattify nested.json -o flat.json

Generate SQL Query

Create SQL to query the JSON:

dct flattify nested.json -s -o query.sql

Inline JSON

Flatten inline JSON:

dct flattify '{"user":{"name":"John","age":30}}'

Flatten JSON array:

dct flattify '[{"a":1},{"b":2}]'

Complex Nested Structure

dct flattify '{"data":{"users":[{"id":1,"profile":{"name":"John"}}]}}' -s

Output Formats

Without -s (Flat JSON)

Converts nested structure to flat key-value pairs using JSONPath-like notation:

Input:

{
  "user": {
    "name": "John",
    "address": {
      "city": "NYC"
    }
  }
}

Output:

{
  "$['user']['name']": "John",
  "$['user']['address']['city']": "NYC"
}

With -s (SQL SELECT)

Generates DuckDB SQL to extract the flattened values:

Input:

[{"a": 1, "b": {"c": 2}}]

Output:

select
    json[0]."a"::decimal,
    json[0]."b"."c"::decimal
from (select '[{"a": 1, "b": {"c": 2}}]'::json as json)

Handling Arrays

Arrays are indexed in the output:

Input:

[1, 2, 3]

Output:

{
  "$[0]": 1,
  "$[1]": 2,
  "$[2]": 3
}

Type Inference

The SQL mode infers types from sample values:

• Numbers → decimal
• Strings → varchar
• Booleans → boolean

Best Practices

• Use -s flag when you need to query the data in SQL
• Flat JSON output is useful for ETL pipelines
• Works with NDJSON files (newline-delimited JSON)
• Handle large files by piping through the command

Integration Examples

With DuckDB

# Generate and execute SQL
dct flattify api_response.json -s | duckdb

# Or save and use
dct flattify data.json -s -o extract.sql
duckdb mydb.duckdb < extract.sql

In Pipeline

# Flatten and convert to CSV
dct flattify nested.json | jq -r 'to_entries[] | [.key, .value] | @csv'

Common Use Cases

API Response Transformation

# Flatten a complex API response
curl -s https://api.example.com/users | dct flattify -s > users.sql

# Query with DuckDB
duckdb -c "$(cat users.sql)"

Document Store to Relational

# Convert MongoDB export to flat format
dct flattify mongodb_export.json -o flat_export.json

Related Skills

• dct-peek: Preview JSON structure before flattening
• dct-infer: Generate schema from flattened data
• dct-js2sql: Convert JSON Schema (not data) to SQL

Limitations

• Very deeply nested structures (>100 levels) may hit limits
• Mixed types in arrays use the first type encountered
• Large files should be processed in chunks