Creating separate list using JOLT

Question

Hello,is it possible to create seperate individual list from one list based on some conditions. e.g i have a list that have category & product information so i have to create individual list based on category/item id with excludeInd flag 0/1,List A - CategoryIds List -> Exclude List B - CategoryIds List -> Include List C - ProductIds List -> Exclude List D -ProductIds List -> Include Input{  "correlationId": "3242a7b4-7929-43b3-9fb6-0d7e173373af",  "sourceSystemId": "PCS",  "messageTimestamp": "2024-05-03T00:00:00",  "id": 1234,  "promotionId": 2303,  "offerId": 2303,  "description": "Members Get Tees $15",  "customerDescription": "Members Get Tees $15",  "levelCode": 0,  "typeCode": 3,  "templateId": 4,  "couponCode": null,  "isCouponCodeRequired": 0,  "startDatetime": "2024-06-20T00:00:00",  "endDatetime": "2024-06-24T23:59:00",  "cancelationDatetime": null,  "comments": null,  "distributionRuleCode": null,  "exclusiveDiscountInd": 0,  "tiers": [    {      "conditions": [        {          "conditionId": 1134,          "buySpendType": 0,          "buySpendValue": 1,          "buyUom": "EA",          "conditionsMerch": [            {              "conditionMerchId": 85339,              "merchandiseLevel": 5,              "deptClass": 0,              "uniqueClass": 0,              "subClass": 0,              "uniqueSubClass": 0,              "item": "613631673",              "categoryId": "",              "diffId": "012",              "excludeInd": 0,              "cancelDatetime": null,              "supplierSite": null,              "brandName": null,              "class": 0            },            {              "conditionMerchId": 85339,              "merchandiseLevel": 5,              "deptClass": 0,              "uniqueClass": 0,              "subClass": 0,              "uniqueSubClass": 0,              "item": "613631674",              "categoryId": "",              "diffId": "012",              "excludeInd": 0,              "cancelDatetime": null,              "supplierSite": null,              "brandName": null,              "class": 0            },            {              "conditionMerchId": 85340,              "merchandiseLevel": 5,              "deptClass": 0,              "uniqueClass": 0,              "subClass": 0,              "uniqueSubClass": 0,              "item": "613631673",              "categoryId": "",              "diffId": "023",              "excludeInd": 1,              "cancelDatetime": null,              "supplierSite": null,              "brandName": null,              "class": 0            },            {              "conditionMerchId": 85341,              "merchandiseLevel": 5,              "deptClass": 0,              "uniqueClass": 0,              "subClass": 0,              "uniqueSubClass": 0,              "item": "",              "categoryId": "613631673",              "diffId": "013",              "excludeInd": 0,              "cancelDatetime": null,              "supplierSite": null,              "brandName": null,              "class": 0            }          ]        }      ],      "rewards": [        {          "rewardId": 8127,          "changeType": 2,          "changeAmount": 15,          "changePercent": null,          "quantityToDiscount": null,          "applyInd": 2,          "rewardsMerch": []        }      ],      "priceRuleAttributes": [        {          "type": "CFA",          "id": "PR_BRANCH_NAME",          "values": [            "MEMBERS"          ]        },        {          "type": "CFA",          "id": "PR_PRICE_RULE_PROMO",          "valueBoolean": true        },        {          "type": "CFA",          "id": "PR_TARGET_SEGMENT",          "values": [            "PAS_LOGGEDIN",            "LOGGEDIN",            "PAS_NOT_LOGGEDIN"          ]        }      ]    },    {      "conditions": [        {          "conditionId": 1135,          "buySpendType": 0,          "buySpendValue": 1,          "buyUom": "EA",          "conditionsMerch": [            {              "conditionMerchId": 85342,              "merchandiseLevel": 5,              "deptClass": 0,              "uniqueClass": 0,              "subClass": 0,              "uniqueSubClass": 0,              "item": "",              "categoryId": "613631673",              "diffId": "012",              "excludeInd": 0,              "cancelDatetime": null,              "supplierSite": null,              "brandName": null,              "class": 0            },            {              "conditionMerchId": 85343,              "merchandiseLevel": 5,              "deptClass": 0,              "uniqueClass": 0,              "subClass": 0,              "uniqueSubClass": 0,              "item": "",              "categoryId": "613631674",              "diffId": "023",              "excludeInd": 1,              "cancelDatetime": null,              "supplierSite": null,              "brandName": null,              "class": 0            }          ]        }      ],      "rewards": [        {          "rewardId": 8128,          "changeType": 2,          "changeAmount": 18,          "changePercent": null,          "quantityToDiscount": null,          "applyInd": 2,          "rewardsMerch": []        }      ],      "priceRuleAttributes": [        {          "type": "CFA",          "id": "PR_BRANCH_NAME",          "values": [            "NON MEMBERS"          ]        },        {          "type": "CFA",          "id": "PR_PRICE_RULE_PROMO",          "valueBoolean": true        }      ]    }  ],  "locations": [    {      "location": 10051,      "cancelDateTime": null,      "action": "INSERT",      "locationType": "S"    }  ],  "attributes": {    "webCategoryInd": false,    "currencyCode": "USD",    "priceType": "PROMOTION",    "createTimestamp": "2024-05-03T00:00:00",    "createUserId": "BRANDIE_YAWS@ANFCORP.COM",    "approvalId": "BRANDIE_YAWS@ANFCORP.COM",    "lastUpdateProgramId": "PCS",    "lastUpdateTimestamp": "2024-05-03T00:00:00",    "lastUpdateUserId": "BRANDIE_YAWS@ANFCORP.COM",    "emergencyInd": false,    "promoTypeCode": "TP"  },  "customAttributes": [    {      "type": "CFA",      "id": "ALLOW_UNIQUE_CD_REPLEN",      "valueBoolean": false    },    {      "type": "CFA",      "id": "ANF_BRAND",      "valueBoolean": false    },    {      "type": "CFA",      "id": "ANF_MENS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "ANF_WOMENS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "APP",      "valueBoolean": false    },    {      "type": "CFA",      "id": "APPLYDEALORDER",      "valueBoolean": false    },    {      "type": "CFA",      "id": "BESTDEAL",      "valueBoolean": false    },    {      "type": "CFA",      "id": "BOGO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CALL_CENTER",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CATEGORY",      "values": [        "1"      ]    },    {      "type": "CFA",      "id": "CND_EQ_RWD",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CND_FLAGS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CND_MRCHTYPE_EXCL_FP",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CND_MRCHTYPE_EXCL_PROMO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CND_MRCHTYPE_EXCL_REDLINES",      "valueBoolean": false    },    {      "type": "CFA",      "id": "CND_TARGET",      "valueBoolean": false    },    {      "type": "CFA",      "id": "ECOMM_BIZ_PROMO_CAT",      "values": [        "Member Pricing"      ]    },    {      "type": "CFA",      "id": "EMPLOYEESELIGIBLE",      "valueBoolean": true    },    {      "type": "CFA",      "id": "EXTERNALNAME",      "values": [        "Members Get Tees $15"      ]    },    {      "type": "CFA",      "id": "FLOOR",      "valueBoolean": false    },    {      "type": "CFA",      "id": "GH_BRAND",      "valueBoolean": false    },    {      "type": "CFA",      "id": "GH_FEMALE",      "valueBoolean": false    },    {      "type": "CFA",      "id": "GH_MALE",      "valueBoolean": false    },    {      "type": "CFA",      "id": "HCO_BRAND",      "valueBoolean": false    },    {      "type": "CFA",      "id": "HCO_GIRLS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "HCO_GUYS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "HOME_OFFICE_ASSOC_PROMO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "KIDS_BOYS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "KIDS_BRAND",      "valueBoolean": false    },    {      "type": "CFA",      "id": "KIDS_GIRLS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "LOGGEDON",      "valueBoolean": false    },    {      "type": "CFA",      "id": "PHYSICAL_STORE",      "valueBoolean": false    },    {      "type": "CFA",      "id": "PR_BRANCH_NAME",      "values": [        "MEMBERS"      ]    },    {      "type": "CFA",      "id": "PR_PRICE_RULE_PROMO",      "valueBoolean": true    },    {      "type": "CFA",      "id": "PR_TARGET_SEGMENT",      "values": [        "1"      ]    },    {      "type": "CFA",      "id": "REDEMPTION_CHANNELS",      "values": [        "2"      ]    },    {      "type": "CFA",      "id": "RWD_FLAGS",      "valueBoolean": false    },    {      "type": "CFA",      "id": "RWD_MRCHTYPE_EXCL_FP",      "valueBoolean": false    },    {      "type": "CFA",      "id": "RWD_MRCHTYPE_EXCL_PROMO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "RWD_MRCHTYPE_EXCL_REDLINES",      "valueBoolean": false    },    {      "type": "CFA",      "id": "RWD_TARGET",      "valueBoolean": false    },    {      "type": "CFA",      "id": "SHIPPING_PROMO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "STORES_BIZ_PROMO_CAT",      "values": [        "NA"      ]    },    {      "type": "CFA",      "id": "UPTO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "WEBCAT_PROMO",      "valueBoolean": false    },    {      "type": "CFA",      "id": "WEB_DESKTOP",      "valueBoolean": false    },    {      "type": "CFA",      "id": "WEB_MOBILE",      "valueBoolean": false    }  ]}Spec[  {    "operation": "shift",    "spec": {      "description": "priceRuleName",      "startDatetime": "startDate",      "endDatetime": "enddate",      "locations": {        "0": {          "location": "storeLocation"        }      },      "customAttributes": {        "*": {          "id": {            "PR_PRICE_RULE_PROMO": {              "@(2,valueBoolean)": "priceRulePromo"            }          }        }      },      "tiers": {        "*": {          "priceRuleAttributes": {            "*": {              "id": {                "PR_BRANCH_NAME": {                  "@(2,values)": "paths.[&5].pathName"                },                "PR_TARGET_SEGMENT": {                  "@(2,values)": "paths.[&5].customerCondition[].targetSegments"                }              }            }          },          "rewards": "paths[&1].calculatePrice",          "conditions": "paths[&1].conditions"        }      }    }  }]Expected Output {  "priceRuleName" : "Members Get Tees $15",  "startDate" : "2024-06-20T00:00:00",  "enddate" : "2024-06-24T23:59:00",  "storeLocation" : 10051,  "priceRulePromo" : true,  "paths" : [ {    "pathName" : [ "MEMBERS" ],    "customerCondition" : [ {      "targetSegments" : [ "PAS_LOGGEDIN", "LOGGEDIN", "PAS_NOT_LOGGEDIN" ]    } ],    "calculatePrice" : [ {      "rewardId" : 8127,      "changeType" : 2,      "changeAmount" : 15,      "changePercent" : null,      "quantityToDiscount" : null,      "applyInd" : 2,      "rewardsMerch" : [ ]    } ],    "catalogCondition": {          "category": [            {              "include": "no",              "categoryList": [                "613631673"              ]            }          ],          "products": [            {              "include": "no",              "productList": [                "613631673",                "613631674"              ]            },            {              "include": "yes",              "productList": [                "613631673"              ]            }          ]        }      }, {    "pathName" : [ "NON MEMBERS" ],    "calculatePrice" : [ {      "rewardId" : 8128,      "changeType" : 2,      "changeAmount" : 18,      "changePercent" : null,      "quantityToDiscount" : null,      "applyInd" : 2,      "rewardsMerch" : [ ]    } ],    "catalogCondition": {          "category": [            {              "include": "no",              "categoryList": [                "613631673",                "613631674"              ]            }          ]        }  } ]}

SAMSAL · Accepted Answer

Hi,Actually I went against the main  input since the second input (simplified version) is not valid json. Hopefully the following will work:  [  {    "operation": "shift",    "spec": {      "description": "priceRuleName",      "startDatetime": "startDate",      "endDatetime": "enddate",      "locations": {        "0": {          "location": "storeLocation"        }      },      "customAttributes": {        "*": {          "id": {            "PR_PRICE_RULE_PROMO": {              "@(2,valueBoolean)": "priceRulePromo"            }          }        }      },      "tiers": {        "*": {          "priceRuleAttributes": {            "*": {              "id": {                "PR_BRANCH_NAME": {                  "@(2,values)": "paths.[&5].pathName"                },                "PR_TARGET_SEGMENT": {                  "@(2,values)": "paths.[&5].customerCondition[#2].targetSegments"                }              }            }          },          "rewards": "paths[&1].calculatePrice",          "conditions": {            "*": {              "conditionsMerch": {                "*": {                  "item": {                    "": {                      "@(2,excludeInd)": {                        //group categories under yes
o include ind                         "0": {                          "@(4,categoryId)": "paths[&9].catalogCondition.category.yes.categoryList[]"                        },                        "1": {                          "@(4,categoryId)": "paths[&9].catalogCondition.category.no.categoryList[]"                        }                      }                    }, //there is a valye in item -> products                    "*": {                      "@(2,excludeInd)": {                        //group products under yes
o include ind                         "0": {                          "@(4,item)": "paths[&9].catalogCondition.products.yes.productList[]"                        },                        "1": {                          "@(4,item)": "paths[&9].catalogCondition.products.no.productList[]"                        }                      }                    }                  }                }              }            }          }        }      }    }  }  ,  {    "operation": "shift",    "spec": {      "*": "&",      "paths": {        "*": {          "*": "&2[&1].&",          "catalogCondition": {            "*": { //products or category              "*": { //bucket each yes
o group into its own array element                "*": "&5[&4].&3.&2[#2].&",                "$": "&5[&4].&3.&2[#2].include"              }            }          }        }      }    }  }  /**/]Also something to consider is using JSLT. JSTL is another json transformation language similar to xquery in syntax ( check reference here) . There is jslt processor in nifi for that. In my openion jslt works better in these situation specially when you trying to query data from very nested structure using some complex logic to match on certain values . You probably have to write less lines in jslt and the logic is easier to follow than jolt (of course if you have get familiar with jslt syntax and how it works first to make sense of it)Here is how this transformation looks using jslt:import "http://jslt.schibsted.com/2018/experimental" as exp let priceRulePromoArray = [for (.customAttributes) .valueBoolean if(.id=="PR_PRICE_RULE_PROMO")]let mainInfo = {                                                    "priceRuleName":.description,                  "startDate":.startDatetime,                  "enddate":.endDatetime,                  "storeLocation":.locations[0].location,                                    "priceRulePromo":$priceRulePromoArray[0]               }let paths= [for(.tiers)                                    {                                          "pathname":flatten([for(.priceRuleAttributes) .values                                  if(.id=="PR_BRANCH_NAME")]),             "customerCondition":[for(.priceRuleAttributes).values                                  if(.id=="PR_TARGET_SEGMENT")],             "calculatePrice": .rewards,             "catalogCondition":[for(.conditions)                                    let groupProductByInd =                                        exp:group-by([for (.conditionsMerch) . if(.item!="")]                                                     ,.excludeInd,                                                     .item)                                  let groupCategoryByInd =                                       exp:group-by([for (.conditionsMerch) . if(.categoryId!="")]                                                   ,.excludeInd                                                   , .categoryId)                               let products = [for ($groupProductByInd )                                                  {                                                    "include": if(.key==0) "yes" else "no",                                                   "productList":.values                                                  }                                               ]                                let category = [for ($groupCategoryByInd )                                                  {                                                     "include": if(.key==0) "yes" else "no",                                                     "categoryList":.values                                                  }                                               ]                                                                                     if(any($products))  {"products":$products} else {} +                                                                                   if(any($category )) {"category":$category} else {}                                                                                 ]             }         ]{"paths":$paths}+$mainInfo

DianaTorres · Answer

@Syed0000 Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @MattWho @SAMSAL who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.