Traversing through two json arrays

Question

I have two json inputs A and B. I need to look for every ProductId under free or paid items array present in A and check if it is present in B and store the result in another new json object. Below is Json A, B and Result i am expecting. Thanks for any help.

Json: A

{
    "merchantAutoBillId": "AB",
    "free": {
        "items": [
            {
                "campaignId": "24_1",            
                "ProductId": "99100",
                "action": "new"
            },
                       {
                "campaignId": "24_1",            
                "ProductId": "99103",
                "action": "new"
            }
        ]
    },
    "paid": {
        "items": [
            {
                "campaignId": "24_1",                
                "ProductId": "99101",
                "action": "new"
            }
        ]
    }
}

Json: B

[{
   "merchantAccountId": "12345678",   
   "status": "Active",     
   "free": {"items": [   {
      "addedDate": "2018-05-10T21:56:24-07:00",    
      "ProductId": "99101",
      "billDate": "2018-05-15T18:00:00+11:00"
   }]},
   "paid": {"items": [   {
      "addedDate": "2018-05-10T21:56:24-07:00",      
      "ProductId": "99102",
      "billDate": "2018-11-08T18:59:59+11:00"      
   },
{
      "addedDate": "2018-05-10T21:56:24-07:00",      
      "ProductId": "99103",
      "billDate": "2018-11-08T18:59:59+11:00"      
   }]}
}]

Result

{"ProductId":[ "99101", "99103"]}`

Answer 1

Best Answer

Oct 11 at 06:14 AM, imtiyaz-qureshi answered with:

@vc5678, Assuming you have these two JSONs in two different flowVars, then you can try below script

 %dw 2.0
 output application/json
 var jsonBProdId = flatten(jsonB..items) groupBy $.ProductId
 ---
 {ProductID: flatten(jsonA..items) filter jsonBProdId[$.ProductId]? map $.ProductId}

Ouput

 {
   "ProductID": [
     "99103",
     "99101"
   ]
 }

alt text

screen-shot-2018-10-10-at-111421-pm.png (398.2 kB)

Add comment

10 |1200 characters needed characters left characters exceeded

Answer 2

hi, @vc5678 You can check with solution .I used flatten and assumed two jso alt text n1 and json2 for this

 %dw 1.0
 %output application/json
 %var json1={
     "merchantAutoBillId": "AB",
     "free": {
         "items": [
             {
                 "campaignId": "24_1",            
                 "ProductId": "99100",
                 "action": "new"
             },
                        {
                 "campaignId": "24_1",            
                 "ProductId": "99103",
                 "action": "new"
             }
         ]
     },
     "paid": {
         "items": [
             {
                 "campaignId": "24_1",                
                 "ProductId": "99101",
                 "action": "new"
             }
         ]
     }
 }
 %var json2={
    "merchantAccountId": "12345678",   
    "status": "Active",     
    "free": {"items": [   {
       "addedDate": "2018-05-10T21:56:24-07:00",    
       "ProductId": "99101",
       "billDate": "2018-05-15T18:00:00+11:00"
    }]},
    "paid": {"items": [   {
       "addedDate": "2018-05-10T21:56:24-07:00",      
       "ProductId": "99102",
       "billDate": "2018-11-08T18:59:59+11:00"      
    },
 {
       "addedDate": "2018-05-10T21:56:24-07:00",      
       "ProductId": "99103",
       "billDate": "2018-11-08T18:59:59+11:00"      
    }]}
 }
  ---
  ProductId:
 flatten (((flatten (flatten json1).items)) filter ((flatten (flatten json2).items).ProductId contains $.ProductId) map (key)->{
      data: key.ProductId 
 } as :array)

Answer 3

Oct 14 at 11:43 PM, vc5678 answered with:

Thanks @sidgo2390sep2018 and @imtiyaz-qureshi for answering.

Add comment

10 |1200 characters needed characters left characters exceeded

Answer 4

Oct 30 at 06:00 AM, vc5678 answered with:

@imtiyaz-qureshi @sidgo2390sep2018 How can i filter on action==new on jsonA as I am only interested in items where action=new I tried var jsonC = jsonA..items filter (action=='new') but doesn't work

Add comment

10 |1200 characters needed characters left characters exceeded

Answer 5

@vc5678

 %dw 1.0
 %output application/json 
 %var firstjson={
     "merchantAutoBillId": "AB",
     "free": {
         "items": [
             {
                 "campaignId": "24_1",            
                 "ProductId": "99100",
                 "action": "new"
             },
                        {
                 "campaignId": "24_1",            
                 "ProductId": "99103",
                 "action": "new"
             }
         ]
     },
     "paid": {
         "items": [
             {
                 "campaignId": "24_1",                
                 "ProductId": "99101",
                 "action": "new"
             }
         ]
     }
 }
 %var secondjson=[{
    "merchantAccountId": "12345678",   
    "status": "Active",     
    "free": {"items": [   {
       "addedDate": "2018-05-10T21:56:24-07:00",    
       "ProductId": "99101",
       "billDate": "2018-05-15T18:00:00+11:00"
    }]},
    "paid": {"items": [   {
       "addedDate": "2018-05-10T21:56:24-07:00",      
       "ProductId": "99102",
       "billDate": "2018-11-08T18:59:59+11:00"      
    },
 {
       "addedDate": "2018-05-10T21:56:24-07:00",      
       "ProductId": "99103",
       "billDate": "2018-11-08T18:59:59+11:00"      
    }]}
 }]
 %var jsonBProdId = flatten (secondjson..items) groupBy $.ProductId
 ---
 {ProductID: flatten (firstjson..items) filter (jsonBProdId[$.ProductId]? and $.action=='new') map $.ProductId}

Traversing through two json arrays

5 Replies

Your answer

Like this question?

Who's following the question

Related Questions