dct-flattify

name

dct-flattify

description

Use this skill when the user wants to flatten nested JSON structures, convert nested objects to flat format, generate SQL queries from nested JSON, unnest hierarchical data, or work with nested API responses that need to be tabular. Triggers include "flatten this json", "make json flat", "nested to flat", "unnest json", "json to sql", "flatten nested", or when dealing with deeply nested JSON from APIs or document stores.

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