JOLT Transformation - Pivot

Question

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 check this link: https://stackoverflow.com/questions/44262429/transform-json-json-jolt — Mohammadreza Khedri, Jul 12 '21 at 09:39
The name in the inner element is irrelevant. I don't need that field. I edited the question and remove that field. @BarbarosÖzhan — Sajad Hamreh, Jul 13 '21 at 06:18

score 1 · Accepted Answer · answered Jul 13 '21 at 18:26

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"
      }
    }
  }
]

You're amazing. I didn't use cardinality before. I'm going to read more about what can be done with it. — Sajad Hamreh, Jul 14 '21 at 06:34

JOLT Transformation - Pivot

1 Answers1