JOLT Transformation: How to Conditionally Merge Two Fields and Retain Other Attributes?

Question

I'm working on a JOLT transformation where I need to merge two fields, AccountNumber and EntityID, retaining only the non-empty value. In my input, one of these fields will always have a value, but never both. Additionally, I want to keep other relevant attributes in the output.

Here’s an example of my simplified input JSON with dummy data:

{
  "AccountNumber": "123456",
  "EntityID": "",
  "TransactionYear": "2023",
  "TransactionMonth": "03",
  "TransactionDay": "05",
  "TransactionHour": "10",
  "TransactionMinutes": "15",
  "TransactionSeconds": "30",
  "ComponentNumber": "12345",
  "Price": "250.00",
  "CurrencyCode": "USD"
}

Objective: Merge AccountNumber and EntityID, keeping only the non-empty value. Since one of these fields will always contain a value, I want the output to reflect that without creating an array. Retain other attributes: Keep TransactionYear, TransactionMonth, TransactionDay, TransactionHour, TransactionMinutes, TransactionSeconds, ComponentNumber, Price, and CurrencyCode in the output.

Here’s the JOLT spec I’m using:

[
  {
    "operation": "shift",
    "spec": {
      "AccountNumber": "Temp.AccountNumber",
      "EntityID": "Temp.EntityID",
      "TransactionYear": "TransactionData.TransactionYear",
      "TransactionMonth": "TransactionData.TransactionMonth",
      "TransactionDay": "TransactionData.TransactionDay",
      "TransactionHour": "TransactionData.TransactionHour",
      "TransactionMinutes": "TransactionData.TransactionMinutes",
      "TransactionSeconds": "TransactionData.TransactionSeconds",
      "ComponentNumber": "NewPrice.ComponentNumber",
      "Price": "NewPrice.SpecialPrice.Price",
      "CurrencyCode": "NewPrice.SpecialPrice.CurrencyCode"
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "AccountNumber": "=firstNotNull(@(1,Temp.AccountNumber), @(1,Temp.EntityID))"
    }
  },
  {
    "operation": "remove",
    "spec": {
      "Temp": ""
    }
  }
]

Problem: The output I’m getting still contains AccountNumber as an array:

{
  "AccountNumber": ["","123456"],
  "TransactionData": {
    "TransactionYear": "2023",
    "TransactionMonth": "03",
    "TransactionDay": "05",
    "TransactionHour": "10",
    "TransactionMinutes": "15",
    "TransactionSeconds": "30"
  },
  "NewPrice": {
    "ComponentNumber": "12345",
    "SpecialPrice": {
      "Price": "250.00",
      "CurrencyCode": "USD"
    }
  }
}

Desired Output: I want AccountNumber to be a single value (either AccountNumber or EntityID), not an array, while retaining all other attributes:

{
  "AccountNumber": "123456",
  "TransactionData": {
    "TransactionYear": "2023",
    "TransactionMonth": "03",
    "TransactionDay": "05",
    "TransactionHour": "10",
    "TransactionMinutes": "15",
    "TransactionSeconds": "30"
  },
  "NewPrice": {
    "ComponentNumber": "12345",
    "SpecialPrice": {
      "Price": "250.00",
      "CurrencyCode": "USD"
    }
  }
}

Question: How can I modify my JOLT spec to ensure that only the first non-empty value between AccountNumber and EntityID is retained without resulting in an array, while still keeping all other attributes?

Thank you in advance!

Answer 1

You can use the following transformation spec :

[
  {
    "operation": "shift",
    "spec": {
      "AccountNumber|EntityID": "Temp.AccountNumber",//form the array named "AccountNumber"
      "*tion*": "&(0,1)tionData.&", //TransactionData -> replicate the first 
                                    //asterisk, eg. "Transact" by &(0,1) 
      "*": "NewPrice.&" //others
    }
  },
  {//combine the components of the "AccountNumber" array 
    "operation": "modify-overwrite-beta",
    "spec": {
      "Temp": {
        "AccountNumber": "=join('',@(1,&))"
      }
    }
  }
]

the demo on the site Jolt Transform Demo Using v0.1.1 is :