arraysjsontransformationjolt

Grouping data with JOLT transformation


I am new to JOLT language and I am having a little headache with a transformation. My JSON input is this one

[
  {
    "table": 0,
    "_time": "2024-05-08T10:33:48.14Z",
    "_value": 40976,
    "parameter": "A",
    "station": "ABC",
    "stationName": "AABBCC"
  },
  {
    "table": 0,
    "_time": "2024-05-08T10:34:33.372Z",
    "_value": 42009,
    "parameter": "A",
    "station": "ABC",
    "stationName": "AABBCC"
  },
  {
    "table": 1,
    "_time": "2024-05-08T10:33:48.141Z",
    "_value": 22091,
    "parameter": "B",
    "station": "XYZ",
    "stationName": "XXYYZZ"
  },
  {
    "table": 1,
    "_time": "2024-05-08T10:34:10.231Z",
    "_value": 21968,
    "parameter": "B",
    "station": "XYZ",
    "stationName": "XXYYZZ"
  }
]

And I am expecting this:

[
  {
    "stationName": "AABBCC",
    "content": [
      {
        "table": 0,
        "_time": "2024-05-08T10:33:48.14Z",
        "_value": 40976,
        "parameter": "A",
        "station": "ABC"
      },
      {
        "table": 0,
        "_time": "2024-05-08T10:34:33.372Z",
        "_value": 42009,
        "parameter": "A",
        "station": "ABC"
      }
    ]
  },
  {
    "stationName": "XXYYZZ",
    "content": [
      {
        "table": 1,
        "_time": "2024-05-08T10:33:48.141Z",
        "_value": 22091,
        "parameter": "B",
        "station": "XYZ"
      },
      {
        "table": 1,
        "_time": "2024-05-08T10:34:10.231Z",
        "_value": 21968,
        "parameter": "B",
        "station": "XYZ"
      }
    ]
  }
]

As you can see, I need the following:

The farthest i can go is with this spec

[
  {
    "operation": "shift",
    "spec": {
      "*": "@station"
    }
    },
  {
    "operation": "shift",
    "spec": {
      "@": "[]"
    }
  }
]

That gives me this:

[
  {
    "ABC": [
      {
        "table": 0,
        "_time": "2024-05-08T10:33:48.14Z",
        "_value": 40976,
        "parameter": "A",
        "station": "ABC",
        "stationName": "AABBCC"
      },
      {
        "table": 0,
        "_time": "2024-05-08T10:34:33.372Z",
        "_value": 42009,
        "parameter": "A",
        "station": "ABC",
        "stationName": "AABBCC"
      }
    ],
    "XYZ": [
      {
        "table": 1,
        "_time": "2024-05-08T10:33:48.141Z",
        "_value": 22091,
        "parameter": "B",
        "station": "XYZ",
        "stationName": "XXYYZZ"
      },
      {
        "table": 1,
        "_time": "2024-05-08T10:34:10.231Z",
        "_value": 21968,
        "parameter": "B",
        "station": "XYZ",
        "stationName": "XXYYZZ"
      }
    ]
  }
]

I dont know if what I am looking for is possible or not to be honest. It is important to clarify that i need to generalize the expression, because the "station" is not always the same.

At least. I would accept this one:

[
  {
    "stationName": "XXYYZZ",
    "ABC": [
      {
        "table": 0,
        "_time": "2024-05-08T10:33:48.14Z",
        "_value": 40976,
        "parameter": "A",
        "station": "ABC"
      },
      {
        "table": 0,
        "_time": "2024-05-08T10:34:33.372Z",
        "_value": 42009,
        "parameter": "A",
        "station": "ABC"
      }
    ],
    "XYZ": [
      {
        "table": 1,
        "_time": "2024-05-08T10:33:48.141Z",
        "_value": 22091,
        "parameter": "B",
        "station": "XYZ"
      },
      {
        "table": 1,
        "_time": "2024-05-08T10:34:10.231Z",
        "_value": 21968,
        "parameter": "B",
        "station": "XYZ"
      }
    ]
  }
]

But i really prefer the previous one

Thank you so much!


Solution

  • Your approach is pretty good but need to take stationName out, not to keep all nested, so we should separately manipulate the stationName attribute. @1,stationName on the RHS will be useful during this manipulation such as

    [
      {
        "operation": "shift",
        "spec": {
          "*": {
            "stationName": "@1,stationName.&", //separate the stationName vs. others
            "*": "@1,stationName.content.&1.&"
          }
        }
      },
      {
        "operation": "shift",
        "spec": {
          "*": {
            "stationName": {
              "0": "&2.&1" //pick only one componet from the array 
            },
            "content": {
              "*": {
                "*": "&3.&2[#2].&" //&2 means grabbing the lieral "content" after going 2 levels up the tree 
              }
            }
          }
        }
      },
      {
        "operation": "shift",
        "spec": {
          "*": "[]"
        }
      }
    ]
    

    the demo on the site https://jolt-demo.appspot.com/ is :

    enter image description here