I am trying to pull orders from one system and post them in a different system using their respective APIs. Each order has a header (with the order number, date, customer, etc) and at least one item, but it could have an infinite number of items.
I have flattened and expanded the JSON that was pulled out of the first system so that the individual line items for each order have their own row and a column for each data point. I have two tables: one with the header information and a second with all the line item rows.
I would now like to put the order header and all of its line items back into a single json body to create the orders in the second system. How can I achieve this? See example below. I would love recommendations for the entire workflow but am most curious about how I can both merge the values in table two based on their Order No field while also indicating what the JSON format should be.