Search code examples
jsonbashshellexport-to-csvjq

Including empty JSON values in jq output

I'm trying to get a .csv out that includes occasional empty values.

Calling this API (https://www.campaignmonitor.com/api/subscribers/#getting-subscribers-details) I get the following:

[
    {
        "ID": "fc0ce7105baeaf97f47c99be31d02a91",
        "Type": "Campaign",
        "Name": "Campaign One",
        "Actions": [
            {
                "Event": "Open",
                "Date": "2010-10-12 13:18:00",
                "IPAddress": "192.168.126.87",
                "Detail": ""
            },
            {
                "Event": "Click",
                "Date": "2010-10-12 13:16:00",
                "IPAddress": "192.168.126.87",
                "Detail": "https://example.com/post/12323/"
            }
        ]
    }
    {
        "ID": "dsadsamdkl9309ujd432",
        "Type": "Campaign",
        "Name": "Campaign Two",
        "Actions": []
    }
]

What I want to get as output:

"Campaign One","Open"
"Campaign One","Click"
"Campaign Two","none"

What I currently get

"Campaign One","Open"
"Campaign One","Click"

I can't seem to find a way to include values when "Actions" == []

What I tried so far:

Attempt 1:

curl -u "apikey:x" https://api.createsend.com/api/v3.2/subscribers/listID/[email protected] | jq -r '.[] | .Name as $n | .Actions[] | ([$n, .Event | if . == null then "none" else . end]) | @csv'

Attempt 2:

curl -u "apikey:x" https://api.createsend.com/api/v3.2/subscribers/listID/[email protected] | jq -r '.[] | .Name as $n | .Actions[] | ([$n, .Event // "none"]) | @csv'

Attempt 3:

curl -u "apikey:x" https://api.createsend.com/api/v3.2/subscribers/listID/[email protected] | jq -r '.[] | .Name as $n | .Actions[] |.Actions[] | if . == [] then .Actions[].Event = "" else . end | ([$n, .Event]) | @csv'

Solution

  • With the alternative operator //:

    jq -r '.[] | (.Actions[].Event // "none") as $e | [ .Name, $e ] | @csv'

    This assumes that the missing comma on line 20 hast been inserted.