generating "table" from json using jq

Bringing back one of my sample data from previous question:

{
  "countries": [
    {
      "countryName": "CZ",
      "cities": [
        {
          "cityName": "Prague",
          "streets": [
            {
              "streetName": "Norská"
            },
            {
              "streetName": "Slovenská"
            }
          ]
        }
      ]
    },
    {
      "countryName": "FR",
      "cities": [
        {
          "cityName": "Paris",
          "streets": [
            {
              "streetName": "rue piat"
            },
            {
              "streetName": "rue lesage"
            }
          ]
        }
      ]
    }
  ]
}

I'd like to use jq to generate plaintext/asciidoc/markdown table from this data, but I miss some basic idea how to do that. The duplication in given column is what I don't get.

So the expected output could look like:

CZ Prague Norská
CZ Prague Slovenská
FR Paris rue piat
FR Paris rue lesage

ie. how to iterate over top level array countries, pick field countryName from it, and iterate over field cities contained in countries array elements and pick cityName from it, etc. to have resultant array of triplets, which can be then processed into desired output text.

EDIT:

updated json to be in sync with expected output data.
meanwhile I was kinda lucky with expression:

jq '.countries[] | .countryName as $country| .cities[] | .cityName as $city | .streets[] | $country+" "+$city+" "+.streetName ' < /tmp/sampleData.json

but following one using @csv did not work for me for some reason.

jq '[(.countries[] | .countryName as $country| .cities[] | .cityName as $city | .streets[] | [$country,$city,.streetName])] | @csv' < /tmp/sampleData.json

I'd be thankful for any suggestion for improvements.

EDIT2: for @csv to work, the it must be fed with array(which it is) but not array of arrays. Misconception of imperative programming style. Anyways, this one is correct:

jq '[(.countries[] | .countryName as $country| .cities[] | .cityName as $city | .streets[] | [$country,$city,.streetName])] | .[] | @csv' < /tmp/sampleData.json

Solution

Your jq solution can be slightly simplified in a way which also enhances its efficiency:

.countries[]
| .countryName as $country
| .cities[]
| .cityName as $city
| .streets[]
| [$country, $city, .streetName]
| @csv

In conjunction with the -r command-line options, this will of course produce valid CSV, but your Q indicates a preference that (unnecessary?) quotation marks be removed. In the case of the sample data shown, you could get away with replacing @csv by join(",") but in general, one has to watch for commas and other characters that are special to CSV.

General case

The following seems to handle the general case but relies on sed to handle the CSV conventions regarding quotation marks and embedded newlines, and has not been thoroughly tested:

< sampleData.json jq -r '
def q:
  if type == "string" and test("[,\n\"]")
  then tojson 
  else . end;
  
.countries[]
| .countryName as $country
| .cities[]
| .cityName as $city
| .streets[]
| [$country, $city, .streetName]
| map(q)
| join(",")
' | sed -e 's/\\\"/""/g' -e 's/\\n/\
/g'