Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JOLT for a optional nested NODE in JSON

I have a JSON from source and I am trying to write a JOLT to flatten this data and convert it in to records so that I can insert it in to a Table.

I have already written a JOLT by referring to my previous queries, but unbale to get desired output.

This question is different from Jolt to transform a multilayered Nested JSON to flat JSON because there the nesting is different and also it do not have optional nodes.

Input JSON

{
  "CustomerMaster": {
    "Rootnode": {
      "KUNNR": "0000028790",
      "NAME1": "NV Solucious",
      "LAND1": "BE",
      "SalesArea": [
        {
          "VKORG": "0200",
          "VTWEG": "00",
          "SPART": "00"
        },
        {
          "VKORG": "4932",
          "VTWEG": "00",
          "SPART": "00",
          "AUFSD1": "FT",
          "PartnerFunction": [
            {
              "PARVW": "ZP",
              "PARZA": "000",
              "KUNN2": "0000028790"
            },
            {
              "PARVW": "ZS",
              "PARZA": "000",
              "KUNN2": "90010523"
            }
          ]
        },
        {
          "VKORG": "4932",
          "VTWEG": "10",
          "SPART": "00",
          "Indicator": {
            "IsDistributionCenter": "X"
          },
          "PartnerFunction": {
            "PARVW": "ZS",
            "PARZA": "000",
            "KUNN2": "90010523"
          }
        }
      ]
    }
  }
}

Desired Output

[ {
  "KUNNR": "0000028790",
  "NAME1": "NV Solucious",
  "LAND1": "BE",,
  "VKORG": "0200",
  "VTWEG": "00",
  "SPART": "00",
}, {
  "KUNNR" : "0000028790",
  "NAME1" : "NV Solucious",
  "LAND1" : "BE",
  "VKORG" : "0200",
  "VTWEG" : "00",
  "SPART" : "00",
  "AUFSD1": "FT",
  "PARVW": "ZP",
  "PARZA": "000",
  "KUNN2": "0000028790"
}, {
  "KUNNR" : "0000028790",
  "NAME1" : "NV Solucious",
  "LAND1" : "BE",
  "VKORG" : "0200",
  "VTWEG" : "00",
  "SPART" : "00",
  "AUFSD1": "FT",
  "PARVW": "ZS",
  "PARZA": "000",
  "KUNN2": "90010523"
}, {
  "KUNNR" : "0000028790",
  "NAME1" : "NV Solucious",
  "LAND1" : "BE",
  "VKORG" : "4932",
  "VTWEG" : "10",
  "SPART" : "00",
  "IsDistributionCenter": "X",
  "PARVW": "ZS",
  "PARZA": "000",
  "KUNN2": "90010523"
}]

I am not getting desired output using the JOLT provided above and need your help to write a JOLT to get desired output.

Tags:

2

Answers


  1. 0

    The answer provided by @BarbarosÖzhan in Jolt to transform a multilayered Nested JSON to flat JSON is nearly perfect; it only misses a small detail for the "Indicator" field.

    Here is the completed Jolt (I only added a few rows):

    [
  {
    "operation": "shift",
    "spec": {
      "CustomerMaster": {
        "Rootnode": {
          "*": "else1.&",
          "SalesArea": {
            "*": {
              "*": "&1.else2.&",
              "Indicator": {       //Added the indicator field
                "*": "&2.else2.&"
              },
              "PartnerFunction": {
                "*": {
                  "*": "&3.&2[&1].&"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "PartnerFunction": {
          "*": {
            "@(3,else1)": { "*": "&4[&1].&" },
            "@(2,else2)": { "*": "&4[&1].&" },
            "*": "&3[&1].&"
          }
        }
      }
    }
  },
  { // get rid of the outermost keys
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]
    Login or Signup to reply.
  2. 0

    Modifying a above jolt provided by @LucaBiscotti and @BarbarosÖzhan little bit to handle all cases.

    [
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "CustomerMaster": {
        "Rootnode": {
          "SalesArea": "=toList"
        }
      }
    }
  }, {
    "operation": "modify-overwrite-beta",
    "spec": {
      "CustomerMaster": {
        "Rootnode": {
          "SalesArea": {
            "*": {
              "PartnerFunction": "=toList"
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "CustomerMaster": {
        "Rootnode": {
          "*": "else1.&",
          "SalesArea": {
            "*": {
              "*": "&1.else2.&",
              "Indicator": { //Added the indicator field
                "*": "&2.else2.&"
              },
              "PartnerFunction": {
                "*": {
                  "*": "&3.&2[&1].&"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "PartnerFunction|*": {
          "*": {
            "@(3,else1)": { "*": "&4[&1].&" },
            "@(2,else2)": { "*": "&4[&1].&" },
            "*": "&3[&1].&"
          }
        }
      }
    }
  },
  { // get rid of the outermost keys
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search