Convert to json object

[heshkaru]

heshan|23|gcp|95|name1|40

heshan|23|gcp|95|name2|41

heshan|23|aws|90|name3|42

heshan|23|aws|90|name4|46

Is it possible to convert the above result set to a JSON Object like below.

{

  "name":"heshan",

  "age":23,

  "experience":[

   {

     "course":"gcp",

     "marks":"95",

     "lecturer":[

      {

        "lname":"name1",

        "lage":40

      },

      {

        "lname":"name2",

        "lage":41

      }

     ]

   },

   {

     "course":"aws",

     "marks":"90",

     "lecturer":[

      {

        "lname":"name3",

        "lage":42

      },

      {

        "lname":"name4",

        "lage":46

      }

     ]

   }

  ]

}

[Anonymous · Accepted solution]

You can do this with a tWriteJSONField component. I have quickly put tan example together using your data. It is configured like this....

 

After this, click the "Configure JSON Tree" button and configure like this...

 

The output looks like this....

{

  "name":"heshan",

  "age":23,

  "experience":[

   {

     "course":"gcp",

     "marks":95,

     "lecturer":[

      {

        "lname":"name1",

        "lage":40

      },

      {

        "lname":"name2",

        "lage":41

      }

     ]

   },

   {

     "course":"aws",

     "marks":90,

     "lecturer":[

      {

        "lname":"name3",

        "lage":42

      },

      {

        "lname":"name4",

        "lage":46

      }

     ]

   }

  ]

}

[heshkaru]

@Richard Hall​ 

Thank you that sorted it.

[heshkaru]

Hi @Richard Hall​ small question,

So i have to use 3 textractjson blocks to extract the data.

Issue is if one of the array blocks is null then the whole json becomes null. Is there a specific way to overcome this, or do I need to use conditions to check whether that element is empty or null and proceed. 

{

  "site_id": "003",

  "purchase_order_details": [

    {

      "item": "2285542",

      "purchase_order_details_notes": [

        {

          "note_line": 1

        }

      ]

    }

  ],

  "purchase_order_allowances": [

    {

      "amount": 23.5

    }

  ]

}

[Anonymous]

That is a different JSON structure to the one you originally asked about. Can you give an example of a null array block that you are dealing with? Is there supposed to be another array inside the "purchase_order_allowances" array?

[heshkaru]

Hi,

purchase_order_details is a nested array inside the json object.

purchase_order_allowances is an array inside the json object.

If all the details are available the process is working. But if either one the array block is null the output is becoming null.

{

    "site_id": "003",

    "purchase_order_details": [

        {

            "item": "2285542",

            "purchase_order_details_notes": [

                {

                    "note_line": "1"

                }

            ]

        }

    ]

}

For example, If we use the above json format the job will fail. (Since inside of textractjsonfield we are converting the object into sub types)

ex:

purchase_order_details (Object) -> item (String) and purchase_order_details_notes(Object)

[Anonymous]

The JSON you provided in your first response was this ....

{

  "site_id":"003",

  "purchase_order_details":[

   {

     "item":"2285542",

     "purchase_order_details_notes":[

      {

        "note_line":1

      }

     ]

   }

  ],

  "purchase_order_allowances":[

   {

     "amount":23.5

   }

  ]

}

In the JSO you show above, it is exactly the same but without the "purchase_order_allowances" array. This array sits at the same level as the "purchase_order_details" array. Since you have two arrays at the same level (and one might be missing) you will need to restructure your job. The first tExtractJSONFields component should be extracting both arrays (as sub JSON objects), you will then need to split the job (potentially with a key to link the data) and extract the remaining data in 2 paths. Afterwards, rejoin the extracted data using the key.

[heshkaru]

Hi any working solution,

Not sure about the whole idea. Is it possible to create 2 separate json objects and merge it into 1?

[Anonymous]

I don't have a working example I can give. But essentially this is what I am saying. In your first tExtractJSONFields component you need to extract "purchase_order_details" and "purchase_order_allowances" as JSON sub-sections and also extract the "site_id". Then go to a tMap and send the "purchase_order_details" column (with "site_id") to one output and the "purchase_order_allowances" (with "site_id") to another output. Then from those two outputs, configure tExtractJSONFields to extract what you need from each and store the rows (including the "site_id") in tHash components.

Then, in a subjob afterwards, simply join the outputs of that using tHash components and a tMap. Join on "site_id".

Why would you need to create JSON here? You are extracting it.

[heshkaru]

We are checking an API for legacy and a new System. We are particularly mapping from one json to its legacy json body with exact values(Keys are changed). Thats why we are converting from 1 json to another.