Creating separate list using JOLT

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5 · Accepted solution]

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

[#6]

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"

[#7]

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"