Jolt specification to transform JSON array within array

Question

I'm trying to transform a JSON file into something that can later be transformed to csv or xml. My JSON file looks like this:

{
  "meta": {
    "contentType": "Response"
  },
  "content": {
    "_type": "Response",
    "data": {
      "_type": "ObjectList",
      "meta": {
        "_type": "ObjectListMeta"
      },
      "DataObjects": [
        {
          "head": {
            "fields": {
              "id": {
                "value": "24"
              },
              "name": {
                "value": ""
              }
            }
          },
          "table": [
            {
              "number": 1,
              "fields": {
                "height": {
                  "value": 500,
                  "text": "500"
                },
                "average": {
                  "value": -2,
                  "text": "-2"
                }
              }
            },
            {
              "number": 2,
              "fields": {
                "height": {
                  "value": 99999,
                  "text": "99999"
                },
                "average": {
                  "value": -5,
                  "text": "-5"
                }
              }
            }
          ]
        }
      ]
    }
  }
}

I want the output to look like this:

[
  {
    "id": "24",
    "name": "",
    "height": 500,
    "average": -2
  },
  {
    "id": "24",
    "name": "",
    "height": 99999,
    "average": -5
  }
]

I tried the following JOLT specification:

[
  {
    "operation": "shift",
    "spec": {
      "content": {
        "data": {
          "DataObjects": {
            "*": {
              "head": {
                "fields": {
                  "id": {
                    "value": "[&4].id"
                  },
                  "name": {
                    "value": "[&4].name"
                  }
                }
              },
              "table": {
                "*": {
                  "fields": {
                    "height": {
                      "value": "[&5].height"
                    },
                    "average": {
                      "value": "[&5].average"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
]

But I only get the following output:

[
  {
    "id": "24",
    "name": "",
    "height" : [ 500, 99999 ],
    "average" : [ -2, -5 ]
  }
]

Which is not exactly what I want. How do I have to modify my spec to get the output I need?

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2021-07-20T13:13:11

You can combine those attributes under the table list as having multiple respective objects for one head.fields object such as

[
  {
    "operation": "shift",
    "spec": {
      "content": {
        "*": {
          "DataObjects": {
            "*": {
              "table": {
                "*": {
                  "@(2,head.fields.id.value)": "[&].id",
                  "@(2,head.fields.name.value)": "[&].name",
                  "@(0,fields.height.value)": "[&].height",
                  "@(0,fields.average.value)": "[&].average"
                }
              }
            }
          }
        }
      }
    }
  }
]

Jolt specification to transform JSON array within array

1 Answers