Jolt transform add index to key

Question

I'm trying to flatten a JSON file for SQL injestion. However, one of the levels is a date and therefore will not match my database (unless I create millions of fields). I need help please.

Original file:

[
  {
    "Transactions": {
      "Sales": {
        "2023-03-31": {
          "Item": "Monitor",
          "Manufacturer": "BenQ",
          "cost": "214.12",
          "currency": "Sterling"
        },
        "2023-03-30": {
          "Item": "Keyboard",
          "Manufacturer": "Dell",
          "cost": "14",
          "currency": "Sterling"
        },
        "2023-03-28": {
          "Item": "Laptop",
          "Manufacturer": "Acer",
          "cost": "840",
          "currency": "Sterling"
        }
      }
    }
  }
]

What I would like it to look like:

[
  {
    "Sale-1-item": "Monitor",
    "Sale-1-Manufacturer": "BenQ",
    "Sale-1-cost": "214.12",
    "Sale-1-currency": "Sterling"
    "Sale-2-Item": "Keyboard",
    "Sale-2-Manufacturer": "Dell",
    "Sale-2-cost": "14",
    "Sale-2-currency": "Sterling"
    "Sale-3-Item": "Laptop",
    "Sale-3-Manufacturer": "Acer",
    "Sale-3-cost": "840",
    "Sale-3-currency": "Sterling"
  }
]

Initially I tried using "Item" : "Item-&1" but this created chaos with items such as "Item-2023-03-01" which would need a column in my database for every day of the year! I then tried following the advice in https://github.com/bazaarvoice/jolt/issues/638 but because the next level up is a wildcard it ends up failing as I can't seem to work out how to use the index in the next level down. Any help very gratefully received. Thank you :)

Answer 1

You can use these successive shift transformations

[
  {
    // convert each innermost attribute to independent arrays
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "*": "&"
            }
          }
        }
      }
    }
  },
  {
    // make each indices object key names
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "&.&2"
        }
      }
    }
  },
  {
    // prefix the keys with those indices along with your literal "Sale"
    "operation": "shift",
    "spec": {
      "*": {
        "*": "Sale-&1-&"
      }
    }
  }
]

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