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.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- SnapLogic - Integration Nation
- Designing and Running Pipelines
- JSON merge objects into 1 array
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
JSON merge objects into 1 array
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 @AleksandarAngel
2 REPLIES 2
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Related Content
- Slicing Data from JSON in Designing and Running Pipelines
- Grab each object from array and insert whole JSON object as a string (VARCHAR) in DB in Designing and Running Pipelines
- Convert array of objects in an object of key value pairs in Designing and Running Pipelines
- Transformation - I need to remove an array inside an object-flatten in Designing and Running Pipelines
- Mapping object, array and timestamp datatypes between SL and Snowflake in Designing and Running Pipelines
