CODEWITHSUNDEEP
jsonapache-nifijolt
Sajad Hamreh
Sajad Hamreh

Reputation: 108

JOLT Transformation - Pivot

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.

Upvotes: 2

Views: 316

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Related Questions