CODEWITHSUNDEEP
jsoncsvjq
glitchtracker
glitchtracker

Reputation: 305

How to convert a JSON with arrays in tsv with jq keeping the array structure?

I need to get the content of a mysql table that has some fields that store data as a JSON array, and ingest it in another database table. I need to use a REST API for that, and it returns the records in JSON. For performance reason, I need that output to be tab tab return. Therefore I use jq's @tsv.

This works greats till it encounters a field that has a JSON array inside, jq then complains "array is not valid in a csv row".

Here's a JSON sample

{
"records": [
 {
        "id": 1,
        "metadata": {
            "description": null,
            "width": 0,
            "height": 0,
            "secondaryColor": "#fff",
            "callToAction": [
                {
                    "link": "/truc.html",
                    "value": "nice",
                    "colors": {
                        "primary": "transparent;",
                        "secondary": "transparent;"
                    }
                }
            ]
        },
        "parent": null
    }
        ]
}

I'd like the following result

1  null  0  0  #fff  [ { "link": "/truc.html", "value": "nice", "colors": { "primary": "transparent;", "secondary": "transparent;" } } ]  null

so basically I want the array to be intact but just returned in a single row

So I wrote this

jq -c --raw-output '.records[]|[.id,.metadata.description,.metadata.width,.metadata.height,.metadata.secondaryColor,.metadata.callToAction,.parent]|@tsv

But I have the "array is not valid in a csv row" error

Is that possible ?

Thanks

Upvotes: 2

Views: 5008

Answers (2)

peak
peak

Reputation: 116870

Since the stated goal includes "null" in the output, you might like to consider mapping all the selected values using tojson, e.g. using map(tojson). With the sample JSON, this would produce:

1   null    0   0   "#fff"  [{"link":"/truc.html","value":"nice","colors":{"primary":"transparent;","secondary":"transparent;"}}]   null

Notice, though, that the string #fff is quoted. If you don't want strings to be quoted in this way, then you might wish to consider:

map(if type == "string" then . else tojson end)

Upvotes: 0

chepner
chepner

Reputation: 531808

You need to JSON-encode the call-to-action first.

% jq -c --raw-output '.records[] |
[.id, .metadata.description, 
      .metadata.width,
      .metadata.height,
      .metadata.secondaryColor,
      (.metadata.callToAction|@json),  # convert this field to JSON first
      .parent
] | @tsv' tmp.json
1       0   0   #fff    [{"link":"/truc.html","value":"nice","colors":{"primary":"transparent;","secondary":"transparent;"}}]

Upvotes: 5

Related Questions