JSONata transpose a dynamic sized object

user_19804669 0
Viewed 30

I have a json response from a REST API that looks like:

{
    "Data": [
        {
            "Name": "MeasurementID1",
            "Samples": [
                {
                    "Time": "2021-12-31T11:15:00.000Z",
                    "Value": "3.280642033",
                },
                {
                    "Time": "2021-12-31T12:15:00.000Z",
                    "Value": "0.06151203811",
                }
            ]
        },
        {
            "Name": "MeasurementID2",
            "Samples": [
                {
                    "Time": "2021-12-31T11:15:00.000Z",
                    "Value": "53.91226196",
                },
                {
                    "Time": "2021-12-31T12:15:00.000Z",
                    "Value": "56.34856796",
                }
            ]
        }
    ]
}

I would like to transform this data for plotting in Grafana where the data is an array of table rows like

[
   { "Time": "2021-12-31T11:15:00.000Z", "MeasurementID1": "3.280642033", "MeasurementID2": "53.91226196".........."MeasurementIDxx": xxx},
   { "Time": "2021-12-31T12:15:00.000Z", "MeasurementID1": "0.06151203811", "MeasurementID2": "56.34856796".........."MeasurementIDxx": xxx}
]

I've hit a stumbling block in that the number of objects in the "Data" array is dynamic and is based on the # requested in rest api get request.

I'm stuck and don't have enough knowledge to do this transformation

jsonata grafana json
2 Answers

If you flattened the structure a bit first, you can use the 'group-by' construct:

Data.Samples.{
    "Name": %.Name,
    "Time": Time,
    "Value": Value
}{Time: $} ~> $each(function($v) {
    $merge($v.{"Time": Time, Name: Value})
})

See https://try.jsonata.org/NXMIg7e0R

user_70791340

I was able to solve it by using 2 nested $reduce calls. Check it out here: https://stedi.link/egfbW8g

$reduce(Data, function($dataAcc, $dataItem) {(
  $reduce($dataItem.Samples, function($samplesAcc, $sampleItem) {(
    $existingItemForTime := $lookup($samplesAcc, $sampleItem.Time);
    $patchForTime := {"Time": $sampleItem.Time, $dataItem.Name: $sampleItem.Value };
    $merge([$samplesAcc, { $sampleItem.Time: $merge([$existingItemForTime, $patchForTime]) }])     
  )}, $dataAcc);
)}, {}) ~> $each(function($v) { $v })
user_8204060
Related