2

I want to format SQLite output in JSON format from the command line. Currently, I have CSV output that looks like this:

label1,value1
label2,value2
label3,value3
...

Now I'd like to have it formatted like this:

{'label1' : 'value1',  'label2': 'value2', ... }

Thanks!

2

3 Answers 3

2

I tested this with GNU awk and sed... not sure if it will with other versions.

awk -F, '{printf "'\''%s'\'' : '\''%s'\'', ", $1, $2} END {print "}" }' input.csv | sed -e 's/^/{/' -e 's/, }/}/' > output.json
2

Valid JSON uses double quotes so:

awk -F, 'BEGIN{print "{"} {if (notfirst) {print ","}; print "\"" $1 "\":\"" $2 "\""; notfirst=1} END {print "\"\":\"\"}" }' input.csv

Also simpler not using sed

0

Assuming we have this:

sqlite> .mode box
sqlite> SELECT * FROM mytable;
┌────────┬────────┐
│ field1 │ field2 │
├────────┼────────┤
│ label1 │ value1 │
│ label2 │ value2 │
│ label3 │ value3 │
└────────┴────────┘

... we can extract the data as JSON with the field1 field renamed key and the field2 field renamed value:

$ sqlite3 mydatabase.db '.mode json' 'SELECT field1 AS key, field2 AS value FROM mytable'
[{"key":"label1","value":"value1"},
{"key":"label2","value":"value2"},
{"key":"label3","value":"value3"}]

This can by passed directly through jq's from_entries filter to create the output you require:

$ sqlite3 mydatabase.db '.mode json' 'SELECT field1 AS key, field2 AS value FROM mytable' | jq from_entries
{
  "label1": "value1",
  "label2": "value2",
  "label3": "value3"
}

Note that the from_entries filter in jq expects an array of "entries", i.e., objects with key and value keys.

Allowing SQLite to output JSON and then handling that JSON with jq ensures that data containing quotes, newlines, and other potentially problematic characters are handled correctly:

sqlite> SELECT * FROM mytable;
┌─────────────────────┬────────────┐
│       field1        │   field2   │
├─────────────────────┼────────────┤
│ label1              │ value1     │
├─────────────────────┼────────────┤
│ label2              │ value2     │
├─────────────────────┼────────────┤
│ label3              │ value3     │
├─────────────────────┼────────────┤
│ label "with quotes" │ value with │
│                     │ newline    │
└─────────────────────┴────────────┘
$ sqlite3 mydatabase.db '.mode json' 'SELECT field1 AS key, field2 AS value FROM mytable' | jq from_entries
{
  "label1": "value1",
  "label2": "value2",
  "label3": "value3",
  "label \"with quotes\"": "value with\nnewline"
}

If you have a pre-existing CSV file created by a SELECT query while .mode csv is active in SQLite, you can still use the same trick with renaming the fields key and value and passing them through from_entries in jq by first using Miller to relabel the fields and converting from CSV to JSON:

$ mlr --c2j label key,value mydata.csv | jq from_entries
{
  "label2": "value2",
  "label3": "value3",
  "label \"with quotes\"": "value with\nnewline"
}

If you have deleted the header line, given mlr the -N option to signify header-less CSV input.

Obviously, if you're already using Miller, you can get it to do all the work without involving jq:

$ mlr --c2j label key,value then put -q 'end { emit @record } @record[$key] = $value' then cut -x -f key,value mydata.csv
[
{
  "label2": "value2",
  "label3": "value3",
  "label \"with quotes\"": "value with\nnewline"
}
]

This reads the data from mydata.csv, relabels the two fields and then collects all the data in a new record whose fields depend on the key field's value in each input record. The two initial key and value fields are then deleted, and the output is created as an array with a single JSON object.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .