Json transformation

[walkerline117]

Ok, here is the original Json

{
“docs”: [
{
“title”: “doc1”,
“description”: “doc1 description”,
“attachment_extension_type”: [
“pdf”
],
“attachment_name”: [
“712210 Regulation Thats Good for Competition.pdf”
],
“attachment_name_pdf_flag”: [
“N”
],
“attachment_size”: [
“510230”
],
“attachment_title”: [
“Quarterly article”
],
“attachment_r_object_id”: [
“0f9ea5bf-10fd-427d-8d34-f8fe4a30d591”
],
“attachment_order_no”: [
“0”
],
“authors_person_id”: [
“1954”,
“4547”
],
“authors_fmno”: [
“1957”,
“4565”
],
“authors_first_name”: [
“Diana”,
“Scott”
],
“authors_preferred_name”: [
“Diana”,
“Scott”
],
“authors_last_name”: [
“Farrell”,
“Beardsley”
],
“authors_vendor_id”: [
“11854”,
“14447”
],
“authors_status”: [
“Alumni”,
“Alumni”
],
“authors_show_alumni_flag”: [
“Y”,
“N”
],
“authors_deceased_flag”: [
“N”,
“N”
],
“timestamp”: “2017-07-24T15:02:19.158Z”,
“no_of_docs”: 0,
“project_id”: 0
}

And here is the output we want

“docs”: {
“description”: “doc1 description”,
“title”: “doc1”,
“authoredDate”: “2005-06-15T16:25:15Z”,
“attachments”: [
{
“name”: “712210 Regulation Thats Good for Competition.pdf”,
“size”: 510230,
“orderNo”: 0,
“pdfFlag”: false,
“objectId”: “0f9ea5bf-10fd-427d-8d34-f8fe4a30d591”,
“title”: “Quarterly article”,
“extensionType”: “pdf”
}
],
“authors”: [
{
“firstName”: “Diana”,
“lastName”: “Farrell”,
“status”: “Alumni”,
“personId”: 1954,
“preferredName”: “Diana”,
“deceasedFlag”: false,
“showAlumniFlag”: “Y”,
“vendorId”: 11854
},
{
“firstName”: “Scott”,
“lastName”: “Beardsley”,
“status”: “Alumni”,
“personId”: 4547,
“preferredName”: “Scott”,
“deceasedFlag”: false,
“showAlumniFlag”: “Y”,
“vendorId”: 14447
}
]
}
}

How I can do such transformation for a ULTRA task?
Any suggestions?

Thanks

[robin]

Hi, just had a quick look at the above. It looks like the original structure defines related information about by the index position in the respective array. So the first value of each of the authors_preferred_name, authors_last_name etc. should be grouped together into the same object in the targeted structure. Similarly for the second, as so on. It’s further complicated by this rule applying separately for attachments and authors (and perhaps others too).

My first thought would be to look into using the Mapper and the expression language’s reduce() function for Arrays.

It’s possible that point you in the right direction.

[tstack · Accepted solution]

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)

[walkerline117]

this really helps. Thanks