Restructure Json Payload

[varun_kasireddy]

Hi All,

I am working on a an interface to query a Datalake/Databricks DB using an API, fetch the records and process them to Target system in Json Format. I have attaced 3 files for reference.

1. Sample source payload from the API Query from the DB - File "SourceDB_Payload.txt"

2. Converted the Source JSon payload to XML format - File "Json_2_xml_Source_Payload.xml"

3. Required Target JSon payload - File "Desired_Target Json_Payload.txt"

Issues am facing: 

1. Source payload has column(fieldnames) names and field values separate as shown below

{
"name": "SYSTEMSOURCECODE",
"type_text": "STRING",
"type_name": "STRING",
"position": 0
},

"EnergySystem",

whereas a normal Json or XML payload will be with the fieldname as shown below 

"SystemSourceCode": "EnergySystem"         or 

<SystemSourceCode>EnergySystem</SystemSourceCode>

2. When I converted the Source JSon to XML, all the values are under xml tag "data_array" which makes it difficult to extract and map it to the required target field.

Please advise how to proceed to this get the desired Json payload as attached.

FYI, below two fields in the target are constants.

"WellNumber": 1,
"CompletionNumber": 1,

In simple terms, My target will look like, when I extract the values under data_array in source Json and add the respective fieldnames

Thanks,

Varun

[ag3silaus]

Hello @varun_kasireddy ,

You can use this groovy script for your desired mapping. 

import com.sap.gateway.ip.core.customdev.util.Message
import groovy.json.JsonSlurper
import groovy.json.JsonOutput
import java.util.HashMap;

def Message processData(Message message) {
    //Body
    // Retrieve source message
    def sourceMessage = message.getBody(String.class)

    // Constants for target message
    def constants = [
        "WellNumber": 1,
        "CompletionNumber": 1
    ]

    // Parse the source message from JSON
    def sourceJson = new JsonSlurper().parseText(sourceMessage)

    // Initialize a list to store target messages
    def targetMessages = []

    // Loop through each data item in the source message
    sourceJson.result.data_array.each { data ->
        def targetMessage = [:]

        // Add constant values to the target message
        constants.each { key, value ->
            targetMessage[key] = value
        }

        // Map data from source to target message fields
        targetMessage.SystemSourceCode = data[0]
        targetMessage.CompletionAPINumber = data[1]
        targetMessage.MeasurementPointNumber = data[2]
        targetMessage.ProductionDate = data[3]
        targetMessage.ProductCode = data[4]

        // Add the target message to the list of target messages
        targetMessages.add(targetMessage)
    }

    // Convert the list of target messages to JSON format
    def targetJson = JsonOutput.toJson(targetMessages)

    // Set the body of the message to the target JSON
    message.setBody(targetJson)

    // Return the modified message
    return message;
}

I hope this helps to you. 

Best Regards,

Burak