jq - How to concatenate an array in json

Question

Struggling with formatting of data in jq. I have 2 issues.

1. Need to take the last array .rental_methods and concatenate them into 1 line, colon separated.
2. @csv doesn't seem to work with my query. I get the error string ("5343") cannot be csv-formatted, only array

jq command is this (without the | @csv)

jq --arg LOC "$LOC" '.last_updated as $lu | .data[]|.[]| $lu, .station_id, .name, .region_id, .address, .rental_methods[]'

JSON:

{
    "last_updated": 1539122087,
    "ttl": 60,
    "data": {
        "stations": [{
            "station_id": "5343",
            "name": "Lot",
            "region_id": "461",
            "address": "Austin",
            "rental_methods": [
                "KEY",
                "APPLEPAY",
                "ANDROIDPAY",
                "TRANSITCARD",
                "ACCOUNTNUMBER",
                "PHONE"
                ]
            }
        ]
    }
}

I'd like the output to end up as:

1539122087,5343,Lot,461,Austin,KEY:APPLEPAY:ANDROIDPAY:TRANSITCARD:ACCOUNTNUMBER:PHONE:,

Answer 1

Using @csv:

jq -r '.last_updated as $lu
  | .data[][]
  | [$lu, .station_id, .name, .region_id, .address, (.rental_methods | join(":")) ]
  | @csv'

What you were probably missing with @csv before was an array constructor around the list of things you wanted in the CSV record.

Answer 2

You could repair your jq filter as follows:

.last_updated as $lu
| .data[][]
| [$lu, .station_id, .name, .region_id, .address, 
   (.rental_methods | join(":"))]
| @csv

With your JSON, this would produce:

1539122087,"5343","Lot","461","Austin","KEY:APPLEPAY:ANDROIDPAY:TRANSITCARD:ACCOUNTNUMBER:PHONE"

... which is not quite what you've said you want. Changing the last line to:

map(tostring) | join(",")

results in:

1539122087,5343,Lot,461,Austin,KEY:APPLEPAY:ANDROIDPAY:TRANSITCARD:ACCOUNTNUMBER:PHONE

This is exactly what you've indicated you want except for the terminating punctuation, which you can easily add (e.g. by appending + "," to the program above) if so desired.