Forum Discussion

Vikas2022's avatar
Vikas2022
New Contributor II
2 years ago

JSON merge objects into 1 array

Hello, 

I have below JSON input, which I am trying to read and map. But facing an issue with concatenation as the input json is not formatted.

 

 

 


JSON Generator

 

 

 

 

[
  {
    "entity": {
      "took": 817,
      "timed_out": false,
      "_shards": {
        "total": 20,
        "successful": 20,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": {
          "value": 18,
          "relation": "eq"
        },
        "max_score": null,
        "hits": []
      },
      "aggregations": {
        "composite_agg": {
          "after_key": {
            "byAccountId": "00100027H3QAK"
          },
          "buckets": [
            {
              "key": {
                "byAccountId": "00100027H3QAK"
              },
              "doc_count": 18,
              "by_customer_id": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "00100027H3QAK",
                    "doc_count": 18,
                    "by_customer_name": {
                      "doc_count_error_upper_bound": 0,
                      "sum_other_doc_count": 0,
                      "buckets": [
                        {
                          "key": "Vis Card Services",
                          "doc_count": 15,
                          "MaxVUs": {
                            "value": 15
                          }
                        },
                        {
                          "key": "Vitoria Card Services SA",
                          "doc_count": 3,
                          "MaxVUs": {
                            "value": 3
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  },
  {
    "entity": {
      "took": 772,
      "timed_out": false,
      "_shards": {
        "total": 20,
        "successful": 20,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": {
          "value": 10000,
          "relation": "gte"
        },
        "max_score": null,
        "hits": []
      },
      "aggregations": {
        "composite_agg": {
          "after_key": {
            "byAccountId": "6516983c9d71be4bcd799313"
          },
          "buckets": [
            {
              "key": {
                "byAccountId": "0010J000027lVFDQA2"
              },
              "doc_count": 19,
              "by_customer_id": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "0010J000027lVFDQA2",
                    "doc_count": 19,
                    "by_customer_name": {
                      "doc_count_error_upper_bound": 0,
                      "sum_other_doc_count": 0,
                      "buckets": [
                        {
                          "key": "Semiconductor Components",
                          "doc_count": 19,
                          "MaxVUs": {
                            "value": 880
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            },
            {
              "key": {
                "byAccountId": "6516983c9d71be4bcd799313"
              },
              "doc_count": 10,
              "by_customer_id": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "6516983c9d71be4bcd799313",
                    "doc_count": 10,
                    "by_customer_name": {
                      "doc_count_error_upper_bound": 0,
                      "sum_other_doc_count": 0,
                      "buckets": [
                        {
                          "key": "asdadasda",
                          "doc_count": 10,
                          "MaxVUs": {
                            "value": 50
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
]

 

 

 

 

 

 

 

Mapper1

jsonPath($, "$entity.aggregations.composite_agg.buckets[*].by_customer_id.buckets[*].key")$src_AccountId
jsonPath($, "$entity.aggregations.composite_agg.buckets[*].by_customer_id.buckets[*].by_customer_name.buckets[*].key")$src_AccountName
jsonPath($, "$entity.aggregations.composite_agg.buckets[*].by_customer_id.buckets[*].by_customer_name.buckets[*].MaxVUs.value")$src_MaxVus
jsonPath($, "$entity.aggregations.composite_agg.buckets[*].by_customer_id.buckets[*].by_customer_name.buckets[*].doc_count")$src_DocCount


Mapper1 Output:

 

 

 

 

[
  {
    "src_AccountId": [
      "00100027H3QAK"
    ],
    "src_AccountName": [
      "Vis Card Services",
      "Vitoria Card Services SA"
    ],
    "src_MaxVus": [
      15,
      3
    ],
    "src_DocCount": [
      15,
      3
    ]
  },
  {
    "src_AccountId": [
      "0010J000027lVFDQA2",
      "6516983c9d71be4bcd799313"
    ],
    "src_AccountName": [
      "Semiconductor Components",
      "asdadasda"
    ],
    "src_MaxVus": [
      880,
      50
    ],
    "src_DocCount": [
      19,
      10
    ]
  }
]

 

 

 

 


Further, I want to exclude, the src_AccountName along with its src_MaxVus & src_DocCount if there is more than 1 src_AccountName for any given src_AccountId, the record to exclude should be based on the lowest src_MaxVus value.
For example, in the above Mapper output, In the first array, for src_AccountId (00100027H3QAK) there are 2 src_AccountName,  src_MaxVus & src_DocCount I want Vis Card Services, 15, 15  in the output.

Script Snap

 

 

 

 

// Ensure compatibility with both JDK 7 and 8 JSR-223 Script Engines
try { load("nashorn:mozilla_compat.js"); } catch(e) { }

// Import the interface required by the Script snap.
importPackage(com.snaplogic.scripting.language);

// Import the serializable Java type we'll use for the output data.
importClass(java.util.LinkedHashMap);
importClass(java.util.ArrayList);

/**
 * Create an object that implements the methods defined by the "ScriptHook"
 * interface.  We'll be passing this object to the constructor for the
 * ScriptHook interface.
 */
var impl = {
    /*
     * These variables (input, output, error, log) are defined by the
     * ExecuteScript snap when evaluating this script.
     */
    input : input,
    output : output,
    error : error,
    log : log,

    /**
     * The "execute()" method is called once when the pipeline is started
     * and allowed to process its inputs or just send data to its outputs.
     *
     * Exceptions are automatically caught and sent to the error view.
     */
    execute : function () {
       this.log.info("Executing Transform Script");
        while (this.input.hasNext()) {
            var inDoc = this.input.next();
            var outDoc = new LinkedHashMap();
              
            var SrcAllAccountid = inDoc.get('src_AccountId');
            var listAllAccountid = new ArrayList();

            var SrcAllAccountName = inDoc.get('src_AccountName');
            var listAllAccountName = new ArrayList();

            var SrcAllMaxVus = inDoc.get('src_MaxVus');
            var listAllMaxVus = new ArrayList();

             var SrcAllDocCount = inDoc.get('src_DocCount');
            var listAllDocCount = new ArrayList();

          var chunks = [];
            var tempAccountid = '';  
             var tempAccountName= '';  
              var tempMaxVus= ''; 
               var tempDocCount= ''; 

           for (var i = 0; i < SrcAllAccountid.length; i++) 
           {
             
              if(SrcAllAccountid[i] !== '')
              {   
                tempAccountid = tempAccountid + SrcAllAccountid[i]; 
                chunks.push(tempAccountid);
               listAllAccountid.add(tempAccountid);
                  tempAccountid = "";  


                  tempAccountName = tempAccountName + SrcAllAccountName[i]; 
                chunks.push(tempAccountName);
               listAllAccountName.add(tempAccountName);
                  tempAccountName = "";  

                  tempMaxVus = tempMaxVus + SrcAllMaxVus[i]; 
                chunks.push(tempMaxVus);
               listAllMaxVus.add(tempMaxVus);
                  tempMaxVus = "";  

                  tempDocCount = tempDocCount + SrcAllDocCount[i]; 
                chunks.push(tempDocCount);
               listAllDocCount.add(tempDocCount);
                  tempDocCount = "";  
              } 
        
            
                
            }
           
 outDoc.put("AccountId",listAllAccountid); 
 outDoc.put("Accountname",listAllAccountName); 
 outDoc.put("MaxVus",listAllMaxVus); 
 outDoc.put("DocCount",listAllDocCount); 
     //  outDoc.put("AccountId",listAllAccountid);  // to print input
      
 
           this.output.write(inDoc, outDoc);
            
        }
        this.log.info("Script executed");
    },

    /**
     * The "cleanup()" method is called after the snap has exited the execute() method
     */
    cleanup : function () {
       this.log.info("Cleaning up")
    }
};

/**
 * The Script Snap will look for a ScriptHook object in the "hook"
 * variable.  The snap will then call the hook's "execute" method.
 */
var hook = new com.snaplogic.scripting.language.ScriptHook(impl);

 

 

 

 

Output:

 

 

 

 

[
  {
    "AccountId": [
      "00100027H3QAK"
    ],
    "Accountname": [
      "Vis Card Services"
    ],
    "MaxVus": [
      "15"
    ],
    "DocCount": [
      "15"
    ]
  },
  {
    "AccountId": [
      "0010J000027lVFDQA2",
      "6516983c9d71be4bcd799313"
    ],
    "Accountname": [
      "Semiconductor Components",
      "asdadasda"
    ],
    "MaxVus": [
      "880",
      "50"
    ],
    "DocCount": [
      "19",
      "10"
    ]
  }
]

 

 

 

 

In below Mapper2 I tried to concatenate the above Script snap result into 1 array for each of the 4 fields so that I could further split it using Json splitter snap but I am missing something because I got below output

[].concat(...$['AccountId'])$AccountId
[].concat(...$['Accountname'])$AccountName
[].concat(...$['DocCount'])$DocCount
[].concat(...$['MaxVus'])$MaxVus

 

 

 

 

[
  {
    "AccountId": [
      "00100027H3QAK"
    ],
    "AccountName": [
      "Vis Card Services"
    ],
    "DocCount": [
      "15"
    ],
    "MaxVus": [
      "15"
    ]
  },
  {
    "AccountId": [
      "0010J000027lVFDQA2",
      "6516983c9d71be4bcd799313"
    ],
    "AccountName": [
      "Semiconductor Components",
      "asdadasda"
    ],
    "DocCount": [
      "19",
      "10"
    ],
    "MaxVus": [
      "880",
      "50"
    ]
  }
]

 

 

 

 

Expected Mapper 2 Output:

 

 

 

 

[
	{
		"AccountId": [
			"00100027H3QAK",
			"0010J000027lVFDQA2",
			"6516983c9d71be4bcd799313"
		],
		"AccountName": [
			"Vis Card Services",
			"Semiconductor Components",
			"asdadasda"
		],
		"DocCount": [
			"15",
			"19",
			"10"
		],
		"MaxVus": [
			"15",
			"880",
			"50"
		]
	}
]

 

 

 

 


Can someone please help to get the expected output with the current design or by avoiding script snap, thanks!
j_angelevski  cjhoward18  bojanvelevski Aleksandar_A