on 03-17-2024 1:18 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
72 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.