Convert JSON with array of objects of unpredictable key-value-pairs to CSV

Question

I've got this JSON:

[
  {
    "id": "1",
    "tag": [
      {
        "k": "name",
        "v": "Luxor"
      }
    ]
  },
  {
    "id": "2",
    "tag": [
      {
        "k": "name",
        "v": "Farflame"
      },
      {
        "k": "magic",
        "v": "30"
      }
    ]
  }
]

I want to create a CSV that has "id", "name" and "magic". If an element doesn't exist, an empty string should be set:

"id","name","magic"
"1","Luxor",""
"2","Farflame","30"

I'm nearly there, but I can't get the empty string if "magic" isn't present:

me@MBA test % jq -r '.[] | [.id, (.tag[] | select(.k=="name") | .v), (.tag[] | select(.k=="magic") | .v)] | @csv' simple.json
"1","Luxor"
"2","Farflame","30"

As you can see, the row with id "1" has only two cells, because there isn't a "magic" element inside of "tag". I tried all kinds of if statements, but wasn't able to find one that does what I want.

Motivation

The toy example above resembles the OSM XML code from OpenStreetMap exports. To import some OpenStreetMap data into a database, converting to a tabular structure is a crucial step. OSM stores all tags for a node (or way or relation) in an array of objects with keys "@k" and "@v"; you cannot predict which keys are present and in what order.

Answer 1

select doesn't scale well. Instead, I would write a function similar to from_entries for converting tag arrays to objects, and use it like this:

def f: map({(.k): .v}) | add;
.[] | [.id] + (.tag | f | [.name, .magic]) | @csv

^{Online demo}