Skip to main content
Alevc
Alevc
Silver ⭐️⭐️
December 1, 2023
Solved

Jolt Transformation returning null values

Hi, I'm trying to get this JOLT working, but in some cases, in the output is printing null values. The qty of itens inside RUBRICS array is dynamic. What am I doing wrong ? 


Payload

 

[
  {
    "ENTITY_DOCUMENT": "11",
    "ENTITY_ID": "",
    "ENTITY_TYPE": "TEST_CARD",
    "CLOSED": true,
    "CALCULATION_DATE": "2023-11-01",
    "RUBRICS": [
      {
        "name": "provisao_TEST_card",
        "classification": "",
        "code": "",
        "amount": 110.08,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      }
    ]
  },
  {
    "ENTITY_DOCUMENT": "12",
    "ENTITY_ID": "",
    "ENTITY_TYPE": "TEST_CARD",
    "CLOSED": true,
    "CALCULATION_DATE": "2023-11-01",
    "RUBRICS": [
      {
        "name": "provisao_TEST_card",
        "classification": "",
        "code": "",
        "amount": 120.08,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      }
    ]
  },
  {
    "ENTITY_DOCUMENT": "13",
    "ENTITY_ID": "",
    "ENTITY_TYPE": "TEST_CARD",
    "CLOSED": true,
    "CALCULATION_DATE": "2023-11-01",
    "RUBRICS": [
      {
        "name": "app_TEST_card",
        "classification": "",
        "code": "",
        "amount": 1301.34,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "cred_cofins_cashback_TEST_card",
        "classification": "",
        "code": "",
        "amount": 140.16,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "consumo_TEST_card",
        "classification": "",
        "code": "",
        "amount": 150.35,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "consumo_TEST_card_manual",
        "classification": "",
        "code": "",
        "amount": 160.94,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "cred_pis_cashback_TEST_card",
        "classification": "",
        "code": "",
        "amount": 170.45,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "breakage_TEST_card",
        "classification": "",
        "code": "",
        "amount": 180.85,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "pis_breakage_TEST_card",
        "classification": "",
        "code": "",
        "amount": 190.01,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "perda_adquirencia_TEST_card",
        "classification": "",
        "code": "",
        "amount": 200.77,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "cofins_breakage_TEST_card",
        "classification": "",
        "code": "",
        "amount": 210.04,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "consumo_TEST_card_cashback",
        "classification": "",
        "code": "",
        "amount": 220.49,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      },
      {
        "name": "cashback_TEST_card",
        "classification": "",
        "code": "",
        "amount": 230.33,
        "type": "",
        "business_unit": "TEST",
        "aggregation_key": ""
      }
    ]
  }
]

 


Specs

 

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "RUBRICS": {
          "*": {
            "@name": "&[&3].RUBRIC",
            "@business_unit": "&[&3].COMPENSATION_GROUP",
            "@classification": "&[&3].CLASSIFICATION",
            "@code": "&[&3].CODE",
            "@amount": "&[&3].AMOUNT",
            "@type": "&[&3].TYPE",
            "@aggregation_key": "&[&3].AGGREGATION_KEY",
            "@(2,ENTITY_TYPE)": "&[&3].ENTITY_TYPE",
            "@(2,ENTITY_ID)": "&[&3].ENTITY_ID",
            "@(2,ENTITY_DOCUMENT)": "&[&3].ENTITY_DOCUMENT",
            "@(2,CALCULATION_DATE)": "&[&3].CALCULATION_DATE"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]

 

 Output
[ {
"RUBRIC" : "provisao_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 110.08,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "11",
"CALCULATION_DATE" : "2023-11-01"
}, {
"RUBRIC" : "provisao_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 120.08,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "12",
"CALCULATION_DATE" : "2023-11-01"
}, {
"RUBRIC" : "app_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 1301.34,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "cred_cofins_cashback_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 140.16,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "consumo_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 150.35,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "consumo_TEST_card_manual",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 160.94,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "cred_pis_cashback_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 170.45,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "breakage_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 180.85,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "pis_breakage_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 190.01,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "perda_adquirencia_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 200.77,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "cofins_breakage_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 210.04,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "consumo_TEST_card_cashback",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 220.49,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
}, null, null, {
"RUBRIC" : "cashback_TEST_card",
"COMPENSATION_GROUP" : "TEST",
"CLASSIFICATION" : "",
"CODE" : "",
"AMOUNT" : 230.33,
"TYPE" : "",
"AGGREGATION_KEY" : "",
"ENTITY_TYPE" : "TEST_CARD",
"ENTITY_ID" : "",
"ENTITY_DOCUMENT" : "13",
"CALCULATION_DATE" : "2023-11-01"
} ]


SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
December 2, 2023

Hi @Alevc ,

It seems the problem with the syntax "&[&3]"  that is used inside the RUBRICS elements transformation. Notice how its always producing an array of three elements for each RUBRICS element index, this happen to matche the number of objects in the parent array at level &3. Since all parent object have at least 1 RUBRIC object at index 0, they will be grouped together under 0, however only the 3rd Parent object has more than one elements so when it try to group under index 1,2,3...10 only one object is found so its adds two null values to the array to keep total at 3. Im not sure why its doing this, I thought I understand jolt but sometimes I find that Im still scratching the surface :).

The good news is I have seen this pattern  before and I know how to resolve. The challenge here is how to combine both child and parent attributes together to form a unique object against each child. You can do this by creating a unique key to help you group each parent & child attributes under one object assigned to that key, that will be the first transformation. The second transformation is to dump all parent & child object into the final array leaving the assigned unique key behind.

The unique key in this case can be combination of parent index and child index. I used underscore (_) to separate.

 

[
  // First transformation to group each parent\child attributes
  // under a unique key using combination of parent index and 
  // chilld index , for example the first child under the first 
  // parent will have 0_0, first child of second parent will be 1_0,
  // 2ed child of the 3rd parent will be 2_1 and so on
  {
    "operation": "shift",
    "spec": {
      "*": {
        "RUBRICS": {
          "*": {
            "@name": "[#].&1_&3.RUBRIC",
            "@business_unit": "[#].&1_&3.COMPENSATION_GROUP",
            "@classification": "[#].&1_&3.CLASSIFICATION",
            "@code": "[#].&1_&3.CODE",
            "@amount": "[#].&1_&3.AMOUNT",
            "@type": "[#].&1_&3.TYPE",
            "@aggregation_key": "[#].&1_&3.AGGREGATION_KEY",
            "@(2,ENTITY_TYPE)": "[#].&1_&3.ENTITY_TYPE",
            "@(2,ENTITY_ID)": "[#].&1_&3.ENTITY_ID",
            "@(2,ENTITY_DOCUMENT)": "[#].&1_&3.ENTITY_DOCUMENT",
            "@(2,CALCULATION_DATE)": "[#].&1_&3.CALCULATION_DATE"
          }
        }
      }
    }
  },
  // Second trabsforamtion is to dump all combined parent &
  // child objects into the final array without the keys.
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[]"
      }
    }
}
  ]

 

If you find this helpful please accept solution.

Thanks

View in thread

2 replies

SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
December 2, 2023

Hi @Alevc ,

It seems the problem with the syntax "&[&3]"  that is used inside the RUBRICS elements transformation. Notice how its always producing an array of three elements for each RUBRICS element index, this happen to matche the number of objects in the parent array at level &3. Since all parent object have at least 1 RUBRIC object at index 0, they will be grouped together under 0, however only the 3rd Parent object has more than one elements so when it try to group under index 1,2,3...10 only one object is found so its adds two null values to the array to keep total at 3. Im not sure why its doing this, I thought I understand jolt but sometimes I find that Im still scratching the surface :).

The good news is I have seen this pattern  before and I know how to resolve. The challenge here is how to combine both child and parent attributes together to form a unique object against each child. You can do this by creating a unique key to help you group each parent & child attributes under one object assigned to that key, that will be the first transformation. The second transformation is to dump all parent & child object into the final array leaving the assigned unique key behind.

The unique key in this case can be combination of parent index and child index. I used underscore (_) to separate.

 

[
  // First transformation to group each parent\child attributes
  // under a unique key using combination of parent index and 
  // chilld index , for example the first child under the first 
  // parent will have 0_0, first child of second parent will be 1_0,
  // 2ed child of the 3rd parent will be 2_1 and so on
  {
    "operation": "shift",
    "spec": {
      "*": {
        "RUBRICS": {
          "*": {
            "@name": "[#].&1_&3.RUBRIC",
            "@business_unit": "[#].&1_&3.COMPENSATION_GROUP",
            "@classification": "[#].&1_&3.CLASSIFICATION",
            "@code": "[#].&1_&3.CODE",
            "@amount": "[#].&1_&3.AMOUNT",
            "@type": "[#].&1_&3.TYPE",
            "@aggregation_key": "[#].&1_&3.AGGREGATION_KEY",
            "@(2,ENTITY_TYPE)": "[#].&1_&3.ENTITY_TYPE",
            "@(2,ENTITY_ID)": "[#].&1_&3.ENTITY_ID",
            "@(2,ENTITY_DOCUMENT)": "[#].&1_&3.ENTITY_DOCUMENT",
            "@(2,CALCULATION_DATE)": "[#].&1_&3.CALCULATION_DATE"
          }
        }
      }
    }
  },
  // Second trabsforamtion is to dump all combined parent &
  // child objects into the final array without the keys.
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[]"
      }
    }
}
  ]

 

If you find this helpful please accept solution.

Thanks

Translate
Alevc
AlevcAuthor
Silver ⭐️⭐️
December 4, 2023

Hi @SAMSAL. Now it is clear to me why it wasn't working. JOLT has a tricky learning curve, isn't it ? 

I do appreciate your attention on helping me. 

Thanks !!!! 

Translate
Terms & ConditionsAccessibility statement