Question 179366 in 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

Json – Mapping values in JOLT and loop for corresponding IDs

I need to transform below Input JSON where the ID has multiple ORG_LVL_2 codes and it should fall under the same OrgUnits array, whereas in my spec a separate object is getting created.

[
  {
    "id": "0000606800",
    "ORG_LVL_2": "Co0053"
  },
  {
    "id": "0000606800",
    "ORG_LVL_2": "Co0054"
  },
  {
    "id": "0000609480",
    "ORG_LVL_2": "Co0055"
  }
]

as Output JSON:

{
  "IntegrationEntities": {
    "integrationEntity": [
      {
        "integrationEntityHeader": {
          "referenceCodeForEntity": "test"
        },
        "integrationEntityDetails": {
          "glAccount": {
            "id": "0000606800",
            "orgUnits": {
              "orgUnitAssignment": [
                {
                  "orgUnitDetails": [
                    {
                      "orgUnitLevel": "ORG_LVL_1",
                      "orgUnitCode": "C01"
                    },
                    {
                      "orgUnitLevel": "ORG_LVL_2",
                      "orgUnitCode": "Co0053"
                    }
                  ]
                },
                {
                  "orgUnitDetails": [
                    {
                      "orgUnitLevel": "ORG_LVL_1",
                      "orgUnitCode": "C01"
                    },
                    {
                      "orgUnitLevel": "ORG_LVL_2",
                      "orgUnitCode": "Co0054"
                    }
                  ]
                }
              ]
            }
          }
        }
      },
      {
        "integrationEntityHeader": {
          "referenceCodeForEntity": "test"
        },
        "integrationEntityDetails": {
          "glAccount": {
            "id": "0000609480",
            "orgUnits": {
              "orgUnitAssignment": [
                {
                  "orgUnitDetails": [
                    {
                      "orgUnitLevel": "ORG_LVL_1",
                      "orgUnitCode": "C01"
                    },
                    {
                      "orgUnitLevel": "ORG_LVL_2",
                      "orgUnitCode": "Co0055"
                    }
                  ]
                }
              ]
            }
          }
        }
      }
    ]
  }
}

This is my spec but the output is not as expected:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "#test": "IntegrationEntities.integrationEntity.[&1].integrationEntityHeader.referenceCodeForEntity",
        "id": "IntegrationEntities.integrationEntity.[&1].integrationEntityDetails.glAccount.id",
        "#ORG_LVL_1": "IntegrationEntities.integrationEntity.[&1].integrationEntityDetails.glAccount.orgUnits.orgUnitAssignment.orgUnitDetails.[0].orgUnitLevel",
        "#CO1": "IntegrationEntities.integrationEntity.[&1].integrationEntityDetails.glAccount.orgUnits.orgUnitAssignment.orgUnitDetails.[0].orgUnitCode",
        "#ORG_LVL_2": "IntegrationEntities.integrationEntity.[&1].integrationEntityDetails.glAccount.orgUnits.orgUnitAssignment.orgUnitDetails.[1].orgUnitLevel",
        "ORG_LVL_2": "IntegrationEntities.integrationEntity.[&1].integrationEntityDetails.glAccount.orgUnits.orgUnitAssignment.orgUnitDetails.[1].orgUnitCode"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "IntegrationEntities": {
        "integrationEntity": {
          "*": {
            "integrationEntityHeader": {
              "referenceCodeForEntity": "IntegrationEntities.integrationEntity.[&2].integrationEntityHeader.referenceCodeForEntity"
            },
            "integrationEntityDetails": {
              "glAccount": {
                "id": "IntegrationEntities.integrationEntity.[&3].integrationEntityDetails.glAccount.id",
                "orgUnits": "IntegrationEntities.integrationEntity.[&3].integrationEntityDetails.glAccount.orgUnits"
              }
            }
          }
        }
      }
    }
  }
]

I will appreciate any help that comes my way. Thank you.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you so much that helped..

    There's minor modification in the input instead of ORG_LVL_2 its "LVL2" but we need the same output in orgUnits: Input:

    [
    {
        "id": "0000606800",
        "status": "Active",
        "name": "Office Supplies",
        "LVL2": "208"
    }
]

    also in the output along with the id, I want to display status, "name" as "glAccountDescription" and same as "glAccountName" along with id which will also become "glAccountCode", required output as below:

    
                    "glAccount": {
                        "id": "00000001",
                        "status": "ACTIVE",
                        "glAccountName": "01 A/P Voucher Logging",
                        "glAccountDescription": "A/P Voucher Logging",
                        "glAccountCode": "0001",
                        "glAccountTypeId": "GLTYPE001",
                        "orgUnits":

  2. 0

    You might construct the spec based on partitioning by id values in the beginning such as

    [
  { // partition by "id" values while hardcoding for the attributes "ORG_LVL_1" and "C01"
    "operation": "shift",
    "spec": {
      "*": {
        "id": "@1,id.&",
        "O*": {
          "#ORG_LVL_1|$": "@2,id.&2.orgUnitLevel",
          "#C01|@": "@2,id.&2.orgUnitCode"
        }
      }
    }
  },
  { // dissipate each component of the independent arrays to their respective indexes 
    // in order to generate the desired objects
    "operation": "shift",
    "spec": {
      "*": {
        "#test": "IntegrationEntities.integrationEntity[#2].integrationEntityHeader.referenceCodeForEntity",
        "$": "IntegrationEntities.integrationEntity[#2].integrationEntityDetails.glAccount.id",
        "*": {
          "*": {
            "*": "IntegrationEntities.integrationEntity[#4].integrationEntityDetails.glAccount.orgUnits.orgUnitAssignment[&2].orgUnitDetails[&].&1"
          }
        }
      }
    }
  },
  { // get rid of redundantly generated null values
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]

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

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search