Yet struggling to parse JSON with jq

First lets discuss why you received that result.

When you use [] to extract items from objects/arrays, it yields a value for every item in that object/array.

.sites[]

produces a result for the every value in your sites array (which in this case there's only one).

Another thing to note, using a comma (,) will yield the delimited values within that expression.

[.site_id,.domain]

The comma here produces two values, the site_id and the domain. But, those values are collected in to an array (as denoted by the square brackets).

Putting this into the next part of the expression

.security.waf.rules[] | [.action_text]

The first part goes through all the rules objects in that array. Then for each of those objects, creates an array containing the action_text. This creates three arrays (one for each of the rules).

Put this together with the previous part of the expression (slightly reformatted)

([.site_id,.domain]) , (.security.waf.rules[] | [.action_text])

This all together produces four arrays, the array containing the site_id and domain, followed by the three arrays of action_text.

Then for each of those four arrays, a csv row is created giving you the results you see.

So how can we get the desired results?

First, we'll want to start go through all the sites. I'm assuming you want a row per site.

.sites[]

Then for each site, we need to build an array of the values in that row. Start with what we have direct access to.

.site_id, .domain

Then produce the action_text values.

.security.waf.rules[].action_text

Note we're not putting the action_text in a separate array, we just want the value.

Now we put those values together.

.site_id, .domain, (.security.waf.rules[].action_text)

This creates five values as we discussed, but we want to collect them in an array so we may pass it to the @csv filter.

[.site_id, .domain, (.security.waf.rules[].action_text)]

Putting everything together will give us this filter:

.sites[] | [.site_id, .domain, (.security.waf.rules[].action_text)] | @csv

Of course there's many approaches you can take to get these values (like building out arrays separately then combining them) but this is the most direct.

All the values to be printed in a CSV row must be collected in a JSON array. A solution that is very similar to your attempt would therefore be:

.sites[]| [.site_id, .domain, (.security.waf.rules[] | .action_text) ]| @csv

By the way, there is no need to use cat:

jq -r -f program.jq test.json