Elastic Mapping with a simple Script Snap

[msager]

An Elastic Mapper which outputs JSON that only contains keys that have non-null values

Background: The use case came while trying to map fields from a Salesforce Outbound Message. This message would send a varying number of fields, from each record, each having non-null values. For example, the SFDC Object had 50 fields in total and one record would return a document with 35 of the 50 possible fields populated and the next record would send 25 and so on; the data is then moved to another database that is pre-populated with data for each record. The problem was that using a Mapper, all 50 fields would need to be mapped in advance, and therefore those fields with NULL values would potentially overwrite data when updating the database.

Our Mapper is static. Target Schemas need to be defined up front. The problem to solve was how to create a Mapper that filters out the non-null fields from each input document so that the update can occur without overwriting data with the null values.

The Magic:

All you need is ONE line of code added to the ‘try’ block of the script snap (javascript in this case) that removes from the input map any elements that have null values:

…

   this.log.info("Executing Transform Script");

    while (this.input.hasNext()) {

        try{

            // Read the next document, wrap it in a map and write out the wrapper

            var doc = this.input.next();



            while (doc.values().remove(null));

            var wrapper = new java.util.HashMap();

            wrapper.put("original", doc);

            this.output.write(doc, doc);     //ß Optional note: I modified this to flatten the output

            this.log.info("Transform Script finished");

          

        }

…

** In Python you can use this construct in two lines (please note the indentation required in python):

while (in_doc.values().remove(None)):
         None

See attached test case pipeline:

Limitations:

This was created to work only with flattened, non-hierarchical structures.

**Elastic Mapping_2017_03_01.slp (9.6 KB)

[tstack]

The Mapper can delete fields based on a JSON-Path and you can do quite a bit with JSON-Path. If you wanted to delete fields whose values were null, you could add a Mapper, set “Pass through” to true, and add one row with this expression:

$…[?(value == null)]

Note that there is nothing in the ‘Target Path’ column for this row.

Here’s a breakdown of the JSON-Path:

$… - This tells the mapper to walk the entire JSON object hierarchy.
[?(value == null)] - This is a conditional that tells the mapper to visit only those fields whose value is null.

[msager]

Excellent, Thanks Tim!

[stephenknilans]

Is there a way to get tims method to work on the entire reference?

We start with:

, {
“value”: {
“AddressType”: {
“value”: null
},
“AddressLine1”: {
“value”: null
},
“City”: {
“value”: null
},
“StateProvince”: {
“value”: null
},
“Zip”: {
“value”: null
},
“Country”: {
“value”: null
}
}

And want nothing to appear.

Tim’s method gives us:

, {
“value”: {
“AddressType”: {},
“AddressLine1”: {},
“City”: {},
“StateProvince”: {},
“Zip”: {},
“Country”: {}
}

[tstack]

stephenknilans:

Is there a way to get tims method to work on the entire reference?

If you wanted to delete objects where the ‘value’ field is null, you can use this path:

$..[?(value instanceof Object && value.value == null)]

The .. part of the path means that the entire hierarchy should be traversed. The [?(...)] portion is a filter that is applied to each object in the hierarchy. If the result of the expression in the filter is true, the object will be deleted, otherwise it is left alone. In this case, we check to see if the object we’re visiting (referenced with the value variable) is an object and if it’s value field is null.

If you wanted to delete fields with empty objects instead, you can tweak the expression to call the isEmpty() method, like so:

$..[?(value instanceof Object && value.isEmpty())]