E9.2 Create nested multi-level JSON payload

sm2005

Member
We have a requirement to create JSON payload with multi-level/nested segments as follows. I was able to get Header and Detail nodes with two headless-detail forms but limited to two levels only. Also tried using power browse with tab pages. Neither generated the required structure. Please suggest if/how to generate such structure.

{
"Transaction": {
"Header": {
"TransactionID": "ProcOrder",
"TransactionRefID": "1CA9A176ECFB",
"SourceSiteID": "Denver",
"SourceSystem": "JDE",
},
"Detail": {
"Plant": "Aurora",
"Order": 101401,
"Product": 2323,
"RecipeID": "",
"BatchQuantity": 2700,
"BatchUOM": "EA",
"Items": [
{
"LineNo": 10,
"Item": 303,
"TotQuantity": 1.2,
"UOM": "KG",
"Allocation": {
"Lot": 20161000,
"Quantity": 1.2
},
},
{
"LineNo": 20,
"Item": 304,
"TotQuantity": 4.8,
"UOM": "KG",
"Allocation": [
{
"Lot": 20161000,
"Quantity": 4,
},
{
"Lot": 201610012,
"Quantity": 0.8,
}
]
}
]
}
}
}
 
It took a bit of work just to get this JSON to be valid so I could "pretty print" it! I'll post this alone in this post, then in another talk about how you might generate this at the end of an orchestration. (EDIT: I had to add an array bracket to the 1.2 quantity allocation in order to have consistent data structure)
JSON: 
{
    "Transaction": {
        "Detail": {
            "BatchQuantity": 2700,
            "BatchUOM": "EA",
            "Items": [
                {
                    "Allocation": [
                        {
                            "Lot": 20161000,
                            "Quantity": 1.2
                        }
                    ],
                    "Item": 303,
                    "LineNo": 10,
                    "TotQuantity": 1.2,
                    "UOM": "KG"
                },
                {
                    "Allocation": [
                        {
                            "Lot": 20161000,
                            "Quantity": 4
                        },
                        {
                            "Lot": 201610012,
                            "Quantity": 0.8
                        }
                    ],
                    "Item": 304,
                    "LineNo": 20,
                    "TotQuantity": 4.8,
                    "UOM": "KG"
                }
            ],
            "Order": 101401,
            "Plant": "Aurora",
            "Product": 2323,
            "RecipeID": ""
        },
        "Header": {
            "SourceSiteID": "Denver",
            "SourceSystem": "JDE",
            "TransactionID": "ProcOrder",
            "TransactionRefID": "1CA9A176ECFB"
        }
    }
}
 
Last edited:
Multipart answer coming! (disclaimer: I don't usually spend significant time on an answer here unless it's something I've run into or think I might. This is one of those exceptions-- this is a really interesting question)

I'd use a SQL query script component (extract the data you need in a "flat" format using simple joins) coupled with a Jython script component (take the flat data and turn it into a structure that matches up to what you need).

This stuff isn't easy! You need to be (or get) comfy with SQL, the SQL DB Script component, script component input/outputs, and finally Jython (or your scripting language of choice) to put everything together how you want it.

BUT you have a swiss army knife in your back pocket, and that is GPT.

Step 1: Here I wrote a simplified 3-tier (header, detail, location) SQL for a single order to be used in a data component with outputs mapped to a "flat" output array (the array is 1 row per record):
SQL: 
SELECT SHDOCO
    ,SDLNID
    ,LILOCN
FROM F4201
INNER JOIN F4211 ON SHDOCO = SDDOCO
    AND SHKCOO = SDKCOO
INNER JOIN F41021 ON SDITM = LIITM
    AND SDMCU = LIMCU
WHERE SHDOCO = ?

Step 2: Turned into a Jython script component script:
Python: 
from com.oracle.e1.common import OrchestrationAttributes
from java.sql import Connection
from java.sql import Statement
import json

def main(orchAttr, sqlConn, inputMap):
    return_hash = {}
    
    #Example SQL Select with java.sql.Connection
    #get the vineyard string from inputs
    doco = inputMap["doco"]
    
    #define the select statement
    selectStmt =  """SELECT SHDOCO
,SDLNID
,LILOCN
FROM F4201
INNER JOIN F4211 ON SHDOCO = SDDOCO
AND SHKCOO = SDKCOO
INNER JOIN F41021 ON SDITM = LIITM
AND SDMCU = LIMCU
WHERE SHDOCO = ?"""

    #create a prepared statement with the SQL
    preparedStmt = sqlConn.prepareStatement(selectStmt)
    
    #set the input value in the parepared statement
    preparedStmt.setString(1, doco)   
    
    #execute the query returning the result set
    resultSet = preparedStmt.executeQuery()   
      
    #use the helper to set the records from the result set to the array variable
    #Example Row - Define these fields in the output array table, name the Data Set Variable Name "outputArrayName"
    #- {DATE_READ=2020/01/21, LOCATION=VIN1, PRECIP=0.0, MOISTURE=0.0, HIGH=60, LOW=40}   
    return_hash = orchAttr.mapResultSetToDataSet(resultSet, "outputArray")
    
    #close the result and statement
    resultSet.close()
    preparedStmt.close()

    return return_hash

Screenshot showing how to map input & outputs:
1713900867458.png

I ran that to get data in the format needed for input to a pure Jython scripting component.
 
Step 3: Finally, the heavy hitter, chatGPT. My prompt:
I have a input file with format {"outputArray": [ { "SHDOCO" : 1910, "SDLNID" : 1000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 1000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 1000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 2000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 2000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 2000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 3000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 3000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 3000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 4000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 4000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 4000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 5000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 5000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 5000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 6000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 6000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 6000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 7000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 7000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 7000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 8000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 8000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 8000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 10000, "LILOCN" : " " }, { "SHDOCO" : 1910, "SDLNID" : 10000, "LILOCN" : "DAMAGED " }, { "SHDOCO" : 1910, "SDLNID" : 10000, "LILOCN" : "USED " }, { "SHDOCO" : 1910, "SDLNID" : 11000, "LILOCN" : " " }]}I want to use the SDLNID field and LILOCN field to correctly deduce how to arrive at this output structure, using JYTHON. { "Transaction": { "Detail": { "Items": [ { "Allocation": [ { "LILOCN": 20161000 } ], "SDLNID": 10 }, { "Allocation": [ { "LILOCN": 20161000 }, { "LILOCN": 201610012 } ], "SDLNID": 20 } ], "SHDOCO": 1910 }, "Header": { "SourceSiteID": "Denver", "SourceSystem": "JDE", "TransactionID": "ProcOrder", "TransactionRefID": "1CA9A176ECFB" } }}
Click to expand...

ChatGPT Reply with a quite brilliant, and steps-explained script:
To transform the input JSON data into the desired output structure using Jython (which is essentially Python running on the Java platform), you'll need to do a bit of data processing. The `SDLNID` field represents different items, and the `LILOCN` field represents the allocations associated with each item. The output structure suggests grouping by `SDLNID` and aggregating the `LILOCN` values into a list under each item.

Here's how you could write this in Jython, bearing in mind that Python syntax is used since Jython's syntax aligns with Python's:

```python
import json

# Sample input JSON as a string
input_json = '''
{
"outputArray": [
{"SHDOCO": 1910, "SDLNID": 1000, "LILOCN": " "},
{"SHDOCO": 1910, "SDLNID": 1000, "LILOCN": "DAMAGED "},
{"SHDOCO": 1910, "SDLNID": 1000, "LILOCN": "USED "},
...
{"SHDOCO": 1910, "SDLNID": 11000, "LILOCN": " "}
]
}
'''

# Load the string into a Python dictionary
data = json.loads(input_json)

# Process the data
output_dict = {
"Transaction": {
"Detail": {
"Items": [],
"SHDOCO": None
},
"Header": {
"SourceSiteID": "Denver",
"SourceSystem": "JDE",
"TransactionID": "ProcOrder",
"TransactionRefID": "1CA9A176ECFB"
}
}
}

# Helper dictionary to collect items by SDLNID
items_dict = {}

# Iterate through each entry in the outputArray
for entry in data["outputArray"]:
sdl_id = entry["SDLNID"]
locn = entry["LILOCN"].strip()
if locn: # filter out empty LILOCN entries
if sdl_id not in items_dict:
items_dict[sdl_id] = {
"SDLNID": sdl_id,
"Allocation": []
}
items_dict[sdl_id]["Allocation"].append({"LILOCN": locn})

# Add collected items to the output
output_dict["Transaction"]["Detail"]["Items"] = list(items_dict.values())

# Assuming SHDOCO is the same for all entries, set it from the first item
if data["outputArray"]:
output_dict["Transaction"]["Detail"]["SHDOCO"] = data["outputArray"][0]["SHDOCO"]

# Convert the output dictionary to JSON
output_json = json.dumps(output_dict, indent=4)
print(output_json)
```

### Explanation:
1. **Data Loading**: Load the input JSON into a Python dictionary.
2. **Data Structure Preparation**: Create a template for the output JSON based on the structure you provided.
3. **Data Aggregation**: Iterate through the `outputArray`, grouping allocations (`LILOCN`) by `SDLNID`. Non-empty `LILOCN` values are trimmed of whitespace and added to the corresponding `SDLNID`.
4. **Output Construction**: Populate the output structure with the processed data.

### Notes:
- This script assumes that the `SHDOCO` is the same for all items in the input array, so it takes the value from the first item.
- Ensure that all your '...' placeholders in the input JSON string are replaced with actual data to run this script without errors.
- The script assumes the input JSON data structure you provided is complete and does not need additional error handling for missing keys or similar issues.

Run this script within a Jython environment or any Python environment to process the input JSON as required.
Click to expand...

This is a great script that is all but ready to be "plugged into" a Jython scripting component. If you're this far with me, you can figure out how to make it work with the default jython template that ships with orchestrator :)


Good luck!
 
SUPER DISCLAIMER: This script might not work as it is, but it gives you an idea of my workflow using ChatGPT to do the "googling and plugging in" of what I need it to do. I usually have to go back & forth once or twice to massage out what I ultimately need. But this process saves hours, days, even weeks of googling/iterating on very complex scripting questions.

ChatGPT has potential to be misused or give bad info, so make sure you're complying with any and all of your company policies regarding its use. Also make sure you don't feed it proprietary or sensitive info. Finally make sure you've read what it's asking you to plug in, and check for any "bad" code or weird-looking libraries.
 
Notwithstanding Dave's work above, although JSON happily allows multilevel nesting, orchestration and steps such as form requests do not. You can pass objects with arrays but this arrays can only hold simple objects. In the input to an Orchestration or Form Request You can't put an array or structure under an array nor can you put a structure under a structure. A string with JSON can be one of those simple objects but you'll need to write scripts to pick such strings out and deal with them separately.
 
DaveWagoner said:
SUPER DISCLAIMER: This script might not work as it is, but it gives you an idea of my workflow using ChatGPT to do the "googling and plugging in" of what I need it to do. I usually have to go back & forth once or twice to massage out what I ultimately need. But this process saves hours, days, even weeks of googling/iterating on very complex scripting questions.

ChatGPT has potential to be misused or give bad info, so make sure you're complying with any and all of your company policies regarding its use. Also make sure you don't feed it proprietary or sensitive info. Finally make sure you've read what it's asking you to plug in, and check for any "bad" code or weird-looking libraries.
Click to expand...
@DaveWagoner I have definitely been using ChatGPT more, in large part because of your advocacy in the forum. It is an extremely valuable resource to look up syntax and providing sample code to go along with it. It is much easier to find what I need using AI than Google, and it is not even close. For a developer, it is all we need to build a solution even if we are not highly familiar with the language. Programming concepts are transferable between languages and ChatGPT helps to fill in the holes on syntax, and expose functionality available in the language.

However, I think it is important for those Citizen Developers who might be trying build more complex solutions, ChatGPT does not replace having some fundamental programming knowledge. Most of the time you will need to adjust the code to work within the Orchestrator UDO you are working with. That means being able to at least have a basic understanding of what the code is doing so you can adjust it to meet your specific requirements.
 
Kevin Long said:
However, I think it is important for those Citizen Developers who might be trying build more complex solutions, ChatGPT does not replace having some fundamental programming knowledge. Most of the time you will need to adjust the code to work within the Orchestrator UDO you are working with. That means being able to at least have a basic understanding of what the code is doing so you can adjust it to meet your specific requirements.
Click to expand...
YES!

(And, I've submitted my presentation for InFocus on using ChatGPT with orchestrator. I'll be very sure to emphasize this)
 
Back
Top