question Creating separate list using JOLT in Support Questions

Creating separate list using JOLT

Syed0000 — Thu, 11 Jul 2024 15:57:39 GMT

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

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

Re: Creating separate list using JOLT

DianaTorres — Thu, 11 Jul 2024 16:05:22 GMT

@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.

Re: Creating separate list using JOLT

SAMSAL — Thu, 11 Jul 2024 17:47:48 GMT

Hi @Syed0000 ,

There is a lot going on in the json input and expected output that is hard to follow what you are trying to do . Can you shorten\simplify your json input by removing un necessary or or repeated info. It would be even better to come up with dummy json that focuses on the issue. That would help me in providing accurate resolution.

Thanks

Re: Creating separate list using JOLT

Syed0000 — Thu, 11 Jul 2024 19:42:54 GMT

Here is simplified input and output json.

Input

{ "tiers": [ { "conditions": [ { "conditionsMerch": [ { "item": "613631673", "categoryId": "", "excludeInd": 0 }, { "item": "613631674", "categoryId": "", "excludeInd": 0 }, { "item": "613631675", "categoryId": "", "excludeInd": 1 }, { "item": "", "categoryId": "123455", "excludeInd": 0 } ] } ] }, { "conditions": [ { "conditionsMerch": [ { "item": "", "categoryId": "613631673", "excludeInd": 0 }, { "item": "", "categoryId": "613631674", "excludeInd": 1 }, { "item": "", "categoryId": "613631675", "excludeInd": 1 } ] } ] } ] }

Output

{ "pricePaths": { "paths": [ { "catalogCondition": { "category": [ { "include": "no", "categoryList": [ "123455" ] } ], "products": [ { "include": "no", "productList": [ "613631673", "613631674" ] }, { "include": "yes", "productList": [ "613631675", ] } ] } }, { "catalogCondition": { "category": [ { "include": "no", "categoryList": [ "613631673" ] }, { "include": "yes", "categoryList": [ "613631674", "613631675" ] } ] } ] } }

Re: Creating separate list using JOLT

SAMSAL — Fri, 12 Jul 2024 14:07:37 GMT

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\no 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\no 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\no 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

Re: Creating separate list using JOLT

Syed0000 — Fri, 12 Jul 2024 16:49:42 GMT

Thanks really appreciated, is it good to use JSLT at java level , any cons as compare to JOLT.

Also how can i get single value for pathName instead of List.

"pathName" : [ "MEMBERS" ] -- >. "pathName" : "MEMBERS"

Re: Creating separate list using JOLT

SAMSAL — Fri, 12 Jul 2024 17:18:19 GMT

For jslt , like I said sometimes its easier to write and work with specially if you are trying to get\aggregate data from heavily nested structure. The other advantage jslt has its ability to traverse nested levels dynamically using recursive function calls for example lets say you are working with json structure but have no idea how many nested level it might have (see my json\jslt answer in the this post to get an idea what I mean by that ). On the other hand jolt appears to perform a little bit better in comparison but they are very close.

In regards to your question you use the follow to extract single value for the path name:

"@(2,values[0])": "paths.[&5].pathName"