how to use jq to print JSON array elements separated by tabs "\t"

Question

I am trying to import a json document to a sqlite3 database using the .import function. from what I can tell, sqlite3 .import expect the JSON data to be one line per row, with number of items matching the table column, and separated by .separate.

if I define .separator="\t", I am trying to create a reformatted json file using jq, but got an error.

here is my test

$echo '[{"a1":{"b1":1,"c1":{"c1":"test","c2":null}}},{"a2":{"b2":1,"c":{"c2":"test","c2":null}}}]' | jq -c '.[] | to_entries'

[{"key":"a1","value":{"b1":1,"c1":{"c1":"test","c2":null}}}]
[{"key":"a2","value":{"b2":1,"c":{"c2":null}}}]

my goal is to create the following tab-separated lines (\t below means tab \x09)

"a1"\t{"b1":1,"c1":{"c1":"test","c2":null}}
"a2"\t{"b2":1,"c":{"c2":null}}

but I failed to achieve this format with jq alone. I could get this separated by new-lines

$echo '[{"a1":{"b1":1,"c1":{"c1":"test","c2":null}}},{"a2":{"b2":1,"c":{"c2":"test","c2":null}}}]' | jq -c '.[] | to_entries |.[] | [.key, .value] | .[]'
"a1"
{"b1":1,"c1":{"c1":"test","c2":null}}
"a2"
{"b2":1,"c":{"c2":null}}

but if I use join("\t"), it complains that it can not join a string with an object

echo '[{"a1":{"b1":1,"c1":{"c1":"test","c2":null}}},{"a2":{"b2":1,"c":{"c2":"test","c2":null}}}]' | jq -c '.[] | to_entries | .[] | [.key, .value] | join("\t")'
jq: error (at <stdin>:1): string ("a1\t") and object ({"b1":1,"c1...) cannot be added

I could use perl or sed to postprocess the new-line separated text to get what I wanted, but I hope to learn how to do this properly with jq.

is this possible?

Answer 1

To get your desired output, use @json to JSON-encode any input as strings. Then, use the --raw-output (or -r) flag to print the string values as is.

The simplest way would be to use string interpolation:

… | jq -r '.[] | to_entries[] | @json "\(.key)\t\(.value)"'

But you can also use join("\t") from your attempt on a prepared array. In this case, using the tojson converter is also possible:

… | jq -r '.[] | to_entries[] | [.key, .value | @json] | join("\t")'
# or
… | jq -r '.[] | to_entries[] | [.key, .value | tojson] | join("\t")'

Output:

"a1"    {"b1":1,"c1":{"c1":"test","c2":null}}
"a2"    {"b2":1,"c":{"c2":null}}