JSON serves as a flexible interchange and storage format for structured or nested data. DuckDB can read it directly, destructure it, flatten it, transform it into relational tables, and store it using a JSON type. section 1.7.1
A practical workflow converts difficult source data into JSON, then into CSV when broader tool compatibility or smaller files matters. DuckDB can also load JSON directly when an intermediate CSV is unnecessary. section 10.1.1
Convert XML through JSON into compressed CSV
Extract selected comment fields with xidel, emit JSON, convert each record to CSV with jq, add a header, and compress the result: `7z e -so stackoverflow.com-Comments.7z | xidel -se '//row/[(@Id|@PostId|@Score|@Text|@CreationDate|@UserId)]' - | (echo "Id,PostId,Score,Text,CreationDate,UserId" && jq -r '. | @csv') | gzip -9 > Comments.csv.gz`. section 10.1.1
Listing 10.1 Converting the XML file via JSON to CSV
7z e -so stackoverflow.com-Comments.7z | \ xidel -se '//row/[(@Id|@PostId|@Score|@Text|@CreationDate|@UserId)]' - | \ (echo "Id,PostId,Score,Text,CreationDate,UserId" && jq -r '. | @csv') | gzip -9 > Comments.csv.gz
Inspect the complete XML-to-JSON-to-CSV command pipeline. section 10.1.1
