Extract properties from multiple JSON arrays using Jolt transformation

Question

My JSON object looks like following:

{
  "array1": [
    {
      "key1": "value1", // common key
      "key2": "value2",
      "key3": "value3"
    },
    {
      "key1": "value1", // common key
      "key2": "value2",
      "key3": "value3"
    }
  ],
  "includes": {
    "array2": [
      {
        "key1": "value1", // common key
        "key4": "value4",
        "key5": "value5"
      },
      {
        "key1": "value1",
        "key4": "value4",
        "key5": "value5"
      }
    ]
  }
}

I need to have the output in following format -

[ 
  {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4", // this comes from joining with array 2 based on key1
    "key5": "value5"  // this comes from joining with array 2 based on key1
  }, 
  {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4", // this comes from joining with array 2 based on key1
    "key5": "value5"  // this comes from joining with array 2 based on key1
  }
]

I only have a solution to fetch fields from array1 but unsure how to join with array2 based on common key, fetch required fields and represent them in a desired way.

Current Transformation :

[
  {
    "operation": "shift",
    "spec": {
      "data": {
        "*": {
          "key1": "[&1].key1",
          "key2": "[&1].key2",
          "key3": "[&1].key3"
        }
      }
    }
  }
]

Current undesired output :

[ 
  {
    "key1" : "value1",
    "key2" : "value2",
    "key3" : "value3"
  }, 
  {
    "key1" : "value1",
    "key2" : "value2",
    "key3" : "value3"
  } 
]

Any help would be appreciated here. Thank you!

user_5841306 · Answer

First of all, in order to get "the undesired output", need to replace "data" with "*" wildcard within the current transformation spec, and no need to repeat each attribute key name and value branch, only using this spec is enough

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "[&1].&"
        }
      }
    }
  }
]

if you'd nest one more level as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": "[&1].&"
          }
        }
      }
    }
  }
]

then, you'd get

[ 
  {
    "key1" : "value1",
    "key4" : "value4",
    "key5" : "value5"
  }, 
  {
    "key1" : "value1",
    "key4" : "value4",
    "key5" : "value5"
  } 
]

We can use "*" and "@" wildcards at different levels of objects in order to combine those results, but this case the values of key name "key1" would repeat of course. We can get rid of that repeating by adding a cardinality transformation , and get your desired result such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": "[&1].&"
          },
          "@": "[&1]"
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE"
      }
    }
  }
]

the demo on the site http://jolt-demo.appspot.com/ is :

Extract properties from multiple JSON arrays using Jolt transformation

1 Answers