jq: map arrays to csv field headers

Question

Is there a way to export a json like this:

{
   "id":"2261026",
   "meta":{
      "versionId":"1",
      "lastUpdated":"2021-11-08T15:13:39.318+01:00",
   },
   "address": [
      "string-value1",
      "string-value2"
   ],
   "identifier":[
      {
         "system":"urn:oid:2.16.724.4.9.20.93",
         "value":"6209"
      },
      {
         "system":"urn:oid:2.16.724.4.9.20.2",
         "value":"00042"
      },
      {
         "system":"urn:oid:2.16.724.4.9.20.90",
         "value":"UAB2"
      }
   ]
}
{
   "id":"2261027",
   "meta":{
      "versionId":"1",
      "lastUpdated":"2021-11-08T15:13:39.318+01:00",
   },
   "address": [
      "string-value1",
      "string-value2",
      "string-value3",
      "string-value4"
   ],
   "identifier":[
      {
         "system":"urn:oid:2.16.724.4.9.20.93",
         "value":"6205"
      },
      {
         "system":"urn:oid:2.16.724.4.9.20.2",
         "value":"05041"
      }
   ]
}

I'd like to get something like this:

"id","meta_versionId","meta_lastUpdated","address","identifier0_system","identifier0_value","identifier1_system","identifier1_value","identifier2_system","identifier2_value"
"2261026","1","2021-11-08T15:13:39.318+01:00","string-value1|string-value2","urn:oid:2.16.724.4.9.20.93","6209","urn:oid:2.16.724.4.9.20.2","00042","urn:oid:2.16.724.4.9.20.90","UAB2"
"2261027","1","2021-11-08T15:13:39.318+01:00","string-value1|string-value2|string-value3|string-value4","urn:oid:2.16.724.4.9.20.93","6205","urn:oid:2.16.724.4.9.20.2","05041",,

In short:

address array field string values has to be mapped joining its values using "|" character. Example: "string-value1|string-value2"

identifiers array field objects have to be mapped to "n-field-header". Example: "identifier0_system","identifier0_value","identifier1_system","identifier1_value","identifier2_system","identifier2_value,..."

Any ideas?

Answer 1

Try this

jq -r '[
  .id,
  (.meta | .versionId, .lastUpdated),
  (.address | join("|")),
  (.identifier[] | .system, .value)
] | @csv'

Demo

---

To prepend a header row with the number of identifierX_system and identifierX_value field pairs in it matching the length of the input's longest identifier array, try this

jq -rs '[
  
  "id",
  "meta_versionId", "meta_lastUpdated",
  "address",
  (
    range([.[].identifier | length] | max)
    | "identifier\(.)_system", "identifier\(.)_value"
  )

], (.[] | [

  .id,
  (.meta | .versionId, .lastUpdated),
  (.address | join("|")),
  (.identifier[] | .system, .value)

]) | @csv'

Demo