How to parse JSON with an inconsistent data type?

Question

I need to create a csv report from a JSON file of the following structure (simplified as much as I saw fit):

{
  "data": [
    {
      "id": "123",
      "foo": {
        "item": {
          "value": "foo_good",
          "bar_id": "1"
        }
      },
      "bar": {
        "item": {
          "id": "1",
          "value": "bar_value_1a"
        }
      },
      "var": {
        "item": {
          "value": "var_value_1a",
          "bar_id": "1"
        }
      }
    },
    {
      "id": "456",
      "foo": {
        "item": [
          {
            "value": "foo_bad",
            "bar_id": "1"
          },
          {
            "value": "foo_good",
            "bar_id": "2"
          },
          {
            "value": "foo_good",
            "bar_id": "2"
          },
          {
            "value": "foo_bad",
            "bar_id": "3"
          },
          {
            "value": "foo_good",
            "bar_id": "4"
          }
        ]
      },
      "bar": {
        "item": [
          {
            "id": "1",
            "value": "bar_value_2a"
          },
          {
            "id": "2",
            "value": "bar_value_2b"
          },
          {
            "id": "3",
            "value": "bar_value_2c"
          },
          {
            "id": "4",
            "value": "bar_value_2d"
          }
        ]
      },
      "var": {
        "item": [
          {
            "value": "var_value_2a",
            "bar_id": "1"
          },
          {
            "value": "var_value_2b",
            "bar_id": "2"
          },
          {
            "value": "var_value_2c",
            "bar_id": "3"
          },
          {
            "value": "var_value_2d",
            "bar_id": "4"
          }
        ]
      }
    }
  ]
}

Data structure:

foo.item and var.item are connected to bar.item with bar_id
One or more foo.item point to one bar.item
Multiple foo.item pointing to the same bar.item will have the same foo.item.value
Exactly one var.item points to one bar.item
All items are sorted by bar_id
The format is inconsistent. bar.item is an object if there is only one item, it's an array otherwise (applies to foo.item and var.item as well)
I am stuck with this format

Output report:

Process all data objects
Create report for each bar.item that does not have any corresponding foo.item with a value foo_bad
The output format: .id, .bar.item.value, .var.item.value

My attempt:

The temporary jq script I am currently using discards multiple bar.item etc. and creates mere estimation of the real data:

.data[]
| .foo.item |= if type=="array" then .[0] else . end
| .bar.item |= if type=="array" then .[0] else . end
| .var.item |= if type=="array" then .[0] else . end
| select(.foo.item.value != "foo_bad")
| [.id,.bar.item.value,.var.item.value]
| @csv

Which outputs the following:

"123","bar_value_1a","var_value_1a"

Desired output:

"123","bar_value_1a","var_value_1a"
"456","bar_value_2b","var_value_2b"
"456","bar_value_2d","var_value_2d"

I prefer using jq although I do not insist on it.

user_997358 · Answer

I would suggest that you begin by ensuring that .foo.item, .bar.item, and .var.item are always arrays. This could be done using the following helper function:

def w: if type=="array" then . else [.] end;

Your jq filter would then begin as follows:

.data[]
| .foo.item |= w
| .bar.item |= w
| .var.item |= w

What you do next depends on what you want to do with all these arrays, but I have verified that the following produces the result you've indicated you want:

.data[]
| .foo.item |= w
| .bar.item |= w
| .var.item |= w
| range(0; .foo.item | length) as $i
| select(.foo.item[$i].value != "foo_bad")
| select(.bar.item[$i].value)
| [.id,.bar.item[$i].value,.var.item[$i].value]
| @csv

How to parse JSON with an inconsistent data type?

1 Answers