I'm trying to transform the following JSON
[
{
"name": "Buy",
"List": [
{
"x": "7/8/2021",
"y": 462853
},
{
"x": "7/9/2021",
"y": 462777
},
{
"x": "7/10/2021",
"y": 462701
}
]
},
{
"name": "Statistical",
"List": [
{
"x": "7/8/2021",
"y": 462853
},
{
"x": "7/9/2021",
"y": 462777
},
{
"x": "7/10/2021",
"y": 462701
}
]
},
{
"name": "Sell",
"List": [
{
"x": "7/8/2021",
"y": 462853
},
{
"x": "7/9/2021",
"y": 462777
},
{
"x": "7/10/2021",
"y": 462701
}
]
}
]
Into something simpler like this, using JOLT:
[
{
"Date": "7/8/2021",
"Buy": 462853,
"Statistical": 462853,
"Sell": 462853
},
{
"Date": "7/9/2021",
"Buy": 462777,
"Statistical": 462777,
"Sell": 462777
},
{
"Date": "7/10/2021",
"Buy": 462701,
"Statistical": 462701,
"Sell": 462701
}
]
I try a lot of jolt code but I can't figure out how to do the last part. I wrote some jolt transformation like this:
[
{
"operation": "shift",
"spec": {
"*": {
"List": {
"*": {
"@(2,name)": "[&(3)].&1.name",
"x": "[&(3)].&1.Date",
"y": "[&(3)].&1.NAV"
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"*": "[]"
}
}
}
]
But I don't know how to do the final part to pivot the name column. The value of the name column should pivot based on NAV value.
You can accumulate the key-value pairs of name keys by x values as represented by Date key within the shift transformation, and then pick leftmost one of the elements of each arrays those already have repeatedly same elements throughout within each by using cardinality transformation such as
[
{
"operation": "shift",
"spec": {
"*": {
"List": {
"*": {
"x": "[&1].Date",
"y": "[&1].@(3,name)"
}
}
}
}
},
{
"operation": "cardinality",
"spec": {
"*": {
"Date": "ONE"
}
}
}
]