This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

JSON merge objects into 1 array

Vikas2022
New Contributor II

‎10-24-2023 12:08 PM - edited ‎10-24-2023 12:50 PM

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 

JSON_Formatting.zip
0 Kudos
Reply
2 REPLIES 2

bojanvelevski
Valued Contributor

‎10-25-2023 05:07 AM

Hey @Vikas2022,

The flow in your pipeline is still object oriented, so you cannot concatenate the arrays at the end. You need some kind of aggregator snap, which in my case I used the GroupBy N snap. Combinated with the reduce function , I believe you'll get the desired output. Check the pipeline and let me know what you think.

 

 

JSON_Formatting_2023_10_25.zip
0 Kudos
Reply

Vikas2022
New Contributor II
In response to bojanvelevski

‎10-25-2023 06:47 AM

Hello @bojanvelevski thanks for the pipeline. However, one scenario is still not covered in the output.
Which is, If for any particular accountId there are more than 2 accountnames then in the final json output I want only accountname,maxvus,doccount for the record whose maxvus is bigger than the other. 

So in given JSON Generator SNAP you can see it is printing only first accountname by default (

Vis Card Services and its maxvus and doccount) but if you change maxvus value to lets say 100 in JSON Generator SNAP for  accountname  (Vitoria Card Services SA) then ideally it should print Vitoria but instead it still prints Vis Card.
0 Kudos
Reply