Cloudera Community

Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search
Solved Go to solution

Jolt Transformation returning null values

Labels:
avatar
author-rank Alevc
Explorer

Created ‎12-01-2023 10:30 AM

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


1,336 Views
0 Kudos
1 ACCEPTED SOLUTION

avatar
author-rank SAMSAL
Super Guru

Created on ‎12-02-2023 06:49 AM - edited ‎12-02-2023 06:55 AM

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 solution in original post

1,313 Views
0 Kudos
2 REPLIES 2

avatar
author-rank SAMSAL
Super Guru

Created on ‎12-02-2023 06:49 AM - edited ‎12-02-2023 06:55 AM

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

1,314 Views
0 Kudos

avatar
author-rank Alevc
Explorer

Created ‎12-04-2023 04:09 AM

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 !!!! 

1,296 Views
0 Kudos
Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements