Jolt转换需求：为JSON键添加索引以适配SQL导入的扁平化处理

I get exactly why those date-based keys are a headache—creating a column for every single date is totally unmanageable. Let's fix this with a multi-step Jolt transform that converts those date-indexed entries into sequentially numbered Sale-N-* fields like you need.

Here's the Jolt Spec You'll Need #

[
  // Step 1: Convert the date-keyed Sales object into an ordered array of entries
  {
    "operation": "shift",
    "spec": {
      "*": {
        "Transactions": {
          "Sales": {
            "*": {
              "@": "salesEntries[]"
            }
          }
        }
      }
    }
  },
  // Optional: Add this step if you need 1-based numbering instead of 0-based
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "salesEntries": {
        "*": {
          "entryIndex": "=intSum($, 1)"
        }
      }
    }
  },
  // Step 2: Map each entry to sequentially numbered fields
  {
    "operation": "shift",
    "spec": {
      "salesEntries": {
        "*": {
          "Item": "Sale-@(1,entryIndex)-Item", // Use "&1" instead of "@(1,entryIndex)" for 0-based
          "Manufacturer": "Sale-@(1,entryIndex)-Manufacturer",
          "cost": "Sale-@(1,entryIndex)-cost",
          "currency": "Sale-@(1,entryIndex)-currency"
        }
      }
    }
  },
  // Step 3: Wrap flattened fields into a single array element (matches your desired output)
  {
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

How This Works Breakdown #

Let's walk through each step to make it clear:

1. Convert Date Keys to Array:
   The first shift operation takes each date-keyed object under Sales and pushes it into a salesEntries array. This turns unstructured date keys into an ordered list, which lets us use array indices for consistent numbering.

2. Adjust for 1-Based Numbering (Optional):
   Jolt uses 0-based indexing by default. If you strictly need 1-based numbering (like Sale-1, Sale-2 as in your example), the modify-overwrite-beta step increments the array index by 1. Skip this if 0-based numbering is acceptable.

3. Map to Sequential Field Names:
   The second shift uses either the raw array index (&1) or our incremented entryIndex to build your desired Sale-N-* field names. Each attribute from the original date entries gets mapped to its numbered counterpart.

4. Final Structure Tweak:
   The last shift wraps all the flattened Sale-N-* fields into a single array element, matching exactly the output format you provided.

Test Result #

When you run your original JSON through this spec (with the 1-based step included), you'll get:

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

This approach avoids the nightmare of date-specific columns and gives you a clean, SQL-friendly flat structure that fits your database schema.

内容的提问来源于stack exchange，提问作者HuwgeEnterprises