Json transformation

There’s a lot going on here, so let’s break it down into several steps:

The first thing I notice is that the values for each property are grouped together in arrays. For example:

    "authors_fmno": [
        "1957",
        "4565"
    ],
    "authors_first_name": [
        "Diana",
        "Scott"
    ],

This grouping is a sign that the sl.zip() function should be used. This function will group together the elements in the arrays. Doing this manually with sl.zip($authors_fmno, $authors_first_name) should return the following:

[
    ["1957", "Diana"],
    ["4565", "Scott"]
]

That should take care of one of the more complicated parts of the transform. However, you probably don’t want to manually go through each property, so we need a way to mix in the property names into the values. Once the property names are mixed in, we can construct the objects using the extend() method. The mapValues() can help with mixing in the names since it can walk through the properties in an object and transform the values using a callback function. The following callback will transform the value of any property that starts with “authors_”. Note that we have to do a map() on the array value to mix in the name for every element.

(value, key) => key.startsWith("authors_") ? value.map(x => [key, x]) : value

Now that we have the callback, we need to do the mapValues() call. Note that I’ve tweaked the key name to remove the “authors_” prefix and I’ve setup the Mapper with a “Mapping Root” of “$docs[*]” so that all elements of the docs array are transformed.

$.mapValues((value, key) => key.startsWith("authors_") ? value.map(x => [key.replace(/^authors_/, ''), x]) : value)

The mapValues() result will still be an object, so we need to get the property values into an array to feed to the extend() method. The jsonPath() function can do that and filter for only the properties with the “authors_” prefix:

jsonPath($, "$[?(key.startsWith('authors_'))]")

At this point, we have the data in a structure that we can feed into the sl.zip() function.

sl.zip.apply(null, jsonPath($, "$[?(key.startsWith('authors_'))]"))

Note that we have to use apply() since sl.zip() expects each array to be passed as a parameter. The apply() function will call the function and pass the array as the arguments to the function.
In the 4.11 release, you can use the spread operator (…) instead:

sl.zip(...jsonPath($, "$[?(key.startsWith('authors_'))]")).map(x => {}.extend(x))

The result of the sl.zip() function will be another array, so we need to map() that to transform each element from an array to an object with the extend() method:

sl.zip.apply(null, jsonPath($, "$[?(key.startsWith('authors_'))]")).map(x => {}.extend(x))

Here’s a pipeline export that demonstrates all of this:

JsonTransformation_2017_11_01.slp (5.2 KB)