JSON Transformation: Flattening Complex Nested JSON Array including empty Objects with JOLT

Question

I am new to JOLT Transformation and I found it really useful in JSON Transformations. But when i come across with a nested and complex JSON I get confused.

The following JSON is a nested and complex JsonArray that I need to transform it into a completely Flat JsonArray.

JSON Input:

[
  {
    "Code": -1,
    "Name": "All",
    "PublisherTypes": [
      {
        "Code": 2,
        "Name": "General",
        "LetterTypes": []
      },
      {
        "Code": 3,
        "Name": "Financials",
        "LetterTypes": []
      }
    ]
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherTypes": [
      {
        "Code": 2,
        "Name": "General",
        "LetterTypes": [
          {
            "Id": 10,
            "Name": "Long Term Info",
            "Code": ""
          },
          {
            "Id": 20,
            "Name": "Short Term Info",
            "Code": ""
          }
        ]
      },
      {
        "Code": 3,
        "Name": "Financials",
        "LetterTypes": [
          {
            "Id": 101,
            "Name": "Total Income",
            "Code": ""
          },
          {
            "Id": 202,
            "Name": "Total Taxes",
            "Code": ""
          }
        ]
      }
    ]
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherTypes": [
      {
        "Code": 2,
        "Name": "General",
        "LetterTypes": [
          {
            "Id": 8,
            "Name": "Monthly Reoprt",
            "Code": ""
          },
          {
            "Id": 58,
            "Name": "Status Report",
            "Code": ""
          }
        ]
      },
      {
        "Code": 3,
        "Name": "Financials",
        "LetterTypes": [
          {
            "Id": 170,
            "Name": "Manager Level",
            "Code": ""
          },
          {
            "Id": 156,
            "Name": "Expert Level",
            "Code": ""
          }
        ]
      }
    ]
  }
]

As you see, we have the Object "LetterTypes":[] that is empty but in the other Json Objects "LetterTypes" has its own Objects and is not empty.

And the following JSON is my Expected Output.

Expected Output:

[
  {
    "Code": -1,
    "Name": "All",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": "",
    "LetterName": "",
    "LetterCode": ""
  },
  {
    "Code": -1,
    "Name": "All",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": "",
    "LetterName": "",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 10,
    "LetterName": "Long Term Info",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 20,
    "LetterName": "Short Term Info",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 101,
    "LetterName": "Total Income",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 202,
    "LetterName": "Total Taxes",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 8,
    "LetterName": "Monthly Reoprt",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 58,
    "LetterName": "Status Reoprt",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 170,
    "LetterName": "Manager Level",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 156,
    "LetterName": "Expert Level",
    "LetterCode": ""
  }
]

What I need is a JOLT Spec to generate above Output for me So That when "LetterTypes" is empty it is displayed in the output with Empty String("") values. So, can anyone provide a JOLT Spec for this problem?

Barbaros Özhan · Accepted Answer · 2022-05-22 07:01:24Z

1

You can use the following shift transformation after applying modify transformation spec, in which;

toInteger converts a quoted value to a unquoted integer if it can such as "12"->12 or "12.7"->12, otherwise it stops processing silently, eg unquoted array brackets [ ] are considered to be convertible, while an array with embedded objects are not considered to be integer of course
We've got individual arrays(or lists) within the second step, and we wanna combine them under a common factor. &2 represents reaching up the two levels by traversing { twice, [&] represents combining all stuff as array at the current level.

, in order to populate the empty arrays with default null("") values for each attributes of LetterTypes array such as

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "*": {
            "Letter*": ["=toInteger",
              [
                {
                  "Id": "",
                  "Name": "",
                  "Code": ""
                }
              ]
            ]
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*Types": { // * wildcard represents one or multiple characters, here "*Types" stands for "PublisherTypes"
          "*": {
            "*Types": {
              "*": {
                "@(4,Code)": "Code",
                "@(4,Name)": "Name", // going 4 levels up to grab the desired value
                "@(2,Code)": "PublisherCode",
                "@(2,Name)": "PublisherName", // going 2 levels up to grab the desired value
                "*": "&(2,1)&" // all attributes under the "LetterTypes" arrays, in &(2,1)& : 2 is for going 2 levels up, 1 represents grabbing the piece("Letter") where * is substituted, and the last & represents the value of the current key name("Code" or "Name") 
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[&].&2"
        }
      }
    }
  }
]

all the attributes are accumulated under the innermost part where the most repeats will occur throughout the whole JSON value.

In the first spec, all individual array with 10 components are determined, and the they're moved to their proper objects within the second spec.

edited May 22, 2022 at 7:01

answered May 21, 2022 at 7:12

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Saeed Mousazadeh Over a year ago

Thanks a lot JOLT Man :) . According to my Expected output two objects with "Code" : -1 should be displayed in the output. I used your JOLT Spec but in the Output there is no object With "Code" : -1 . Am i missing something here?

Barbaros Özhan Over a year ago

Yes, you're right @SaeedMousazadeh lately realised the issue. I'll be trying to fix whenever I'm available.

Barbaros Özhan Over a year ago

I've fixed that issue just now @SaeedMousazadeh

Saeed Mousazadeh Over a year ago

JOLT man FOREVER :). You Are lifesaver. Thanks for Precise Explanation.

Saeed Mousazadeh Over a year ago

There are two statements that are not clear enough for me. 1. What is the functionality of toInteger function in the first spec? 2. What does "@": "[&].&2" do in third spec?

|

Collectives™ on Stack Overflow

JSON Transformation: Flattening Complex Nested JSON Array including empty Objects with JOLT

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related