json to csv issue using jq

Question

I using jq to convert below json to CSV according to my necessity

my json is:

{
    "id": 39,
    "max_x": null,
    "max_y": null,
    "min_x": null,
    "min_y": null,
    "name": "L1",
    "space_count": 159,
    "spaces": [
               {
            "account_name": "Westpac",
            "ext_ids": [
                "5d6043ce3b1a6903ba000021",
                "5d6043ce3b1a6903ba000022"
            ],
            "gla": 232,
            "gross_rent": 493565.04000000004,
            "id": 8955,
            "lease_end_time": "2021-09-15T00:00:00.000Z",
            "map_unit_no": "GD030A",
            "mat_excl_gst": 0,
            "mat_growth_rate": null,
            "mat_month": "January-2020",
            "net_rent": 337459.2,
            "unit_no": "030A",
            "vertices": [
            ]
        }
    ],
    "svg": null
}

I try to convert to CSV By using this code in terminal:

cat data1.json | jq -r '.spaces[] | [.account_name,.ext_ids[]] | @csv' | tr -d '"' >> output.csv

result is:

Westpac,5d6043ce3b1a6903ba000021,5d6043ce3b1a6903ba000022

But I need to have this result:

Westpac,5d6043ce3b1a6903ba000021
Westpac,5d6043ce3b1a6903ba000022

Could you please guid me?

Answer 1

Below is a brief explanation from JQ's manual on why this happens.

the expression [1,2,3] is not using a built-in syntax for comma-separated arrays, but is instead applying the [] operator (collect results) to the expression 1,2,3 (which produces three different results).

Using a variable you can circumvent this issue.

.spaces[] | .ext_ids[] as $e | [.account_name, $e] | @csv