Make JQ transform an array of objects to a string

Question

I have a JSON file:

{
  "ClassIdentifier": "consumer-leads",
  "StateMode": "txt-2300",
  "StateGroups": []
}
{
  "ClassIdentifier": "main",
  "StateMode": null,
  "StateGroups": [
    {
      "Status": "active",
      "StateGroupName": "default"
    },
    {
      "Status": "active",
      "StateGroupName": "brown-space"
    },
    {
      "Status": "active",
      "StateGroupName": "txt-hosts"
    }
  ]
}
{
  "ClassIdentifier": "paid-media",
  "StateMode": "txt-2300",
  "StateGroups": []
}
{
  "ClassIdentifier": "reports",
  "StateMode": null,
  "StateGroups": [
    {
      "Status": "active",
      "StateGroupName": "txt-hosts"
    },
    {
      "Status": "active",
      "StateGroupName": "grey-space"
    },
    {
      "Status": "active",
      "StateGroupName": "default"
    }
  ]
}

The output I need:

consumer-leads,txt-2300,null
main,null,brown-space|default|txt-hosts
paid-media,txt-2300,null
reports,null,default|grey-space|txt-hosts

Note that StateGroups (if they exist at all) are sorted by StateGroupName as (or before) they are being transformed into a string of values separated by vertical bars.

What I have tried has given me partial results, but nothing really does the job:

cat json_file |
      jq -r '[ .ClassIdentifier,
               .StateMode,
               .StateGroups[]
             ]'

cat json_file |
      jq -r '{ ClassIdentifier,
               StateMode
             } +
             ( .StateGroups[] | { StateGroupName,  Status } )
             '

cat json_file |
      jq -r ' [ .ClassIdentifier,
              .StateMode,
              .StateGroups |= sort_by( .StateGroupName )
             ]'

UPDATE: We have to use JQ 1.3, so please keep that in mind for a response.

Answer 1

This should work:

[
    .ClassIdentifier,
    .StateMode // "null",
    (.StateGroups
        | map(select(.Status=="active").StateGroupName)
        | sort
        | join("|")
        | if .=="" then "null" else . end
    )
] | @csv

Which produces:

"consumer-leads","txt-2300","null"
"main","null","brown-space|default|txt-hosts"
"paid-media","txt-2300","null"
"reports","null","default|grey-space|txt-hosts"

Note that since you're using 1.3, join/1 won't be available to you. But it shouldn't be difficult to implement yourself.

def join(sep): sep as $sep
    | reduce .[1:][] as $item (.[0]|tostring; . + $sep + $item)
    ;