API Json to Oracle table

Question

Hi,

I have to read API and write it to Oracle database.

I am using Copy data activity and reading the API in Source and oracle in Sink.

I need suggestion on writing the Mapping.

Below is the JSON:-

{
    "d": {
        "results": [
            {
                "__metadata": {
                    "id": "https://bill.eu/lite('74')",
                    "uri": "https://bill.eu/lite('74')",
                    "type": "SRV.GetProduct"
                },
                "Productno": "74",
                "FixedPrice": "120",
				"to_GtinSalesOrg": {
                                "results": [
                                    {
                                        "__metadata": {
                                            "id": "https://bill.eu/data('11')",
                                            "uri": "https://bill.eu/data('11')",
                                            "type": "API.GetGtinSaleOrg"
                                        },
                                        "SalesOrg": "11",
                                        "MainGtin": "4126"
                                    },
                                    {
                                        "__metadata": {
                                            "id": "https://bill.eu/data('12')",
                                            "uri": "https://bill.eu/data('12')",
                                            "type": "API.GetGtinSaleOrg"
                                        },
                                        "SalesOrg": "12",
                                        "MainGtin": "4125"
                                    }
                                ]
                            }
                        }
                    ]
    }
}

Need to write this data in to table like below:-

Productno  FixedPrice   	SalesOrg   			MainGtin
74 			120			01||11||02||12		01||4126||02||4125

01 and 02 is the sequence number and '||' is double pipe.

Answer 1

To change the Json file data as per your requirement, you can use dataflow transformation. Below are the detailed steps to do in dataflow.

• Take the source transformation with the dataset containing Json data.
• Then to flatten the Json, take the flatten transformation. Unroll the Json by the array d.results.to_GtinSalesOrg.results. Give the mapping as,

Productno = d.results.Productno,
FixedPrice = d.results.FixedPrice,
SalesOrg = d.results.to_GtinSalesOrg.results.SalesOrg,
MainGtin = d.results.to_GtinSalesOrg.results.MainGtin

• Next step is to add the row number for each line item within the Productno and fixed price fields. Take the Window transformation. Aggregate over the fields Productno and fixedPrice. Sort by SalesOrg or MainGtin column. Give the aggregate column as row_num and the expression for that column as rowNumber()

over: (Productno,FixedPrice),
sort: (SalesOrg),
Windows column: row_num = rowNumber()

• Then to take all Salesorg and MainGtin values to a single line for each ProductNo and FixedPrice, you can aggregate them using aggregate transformation.

groupBy: (Productno, FixedPrice)
Aggregates:
SalesOrg = collect(toString(row_num)+'||'+SalesOrg),
MainGtin = collect(toString(row_num)+'||'+MainGtin)

• Result of aggregate transformation will contain array of values in SalesOrg and mainGtin. To convert them into double pipe symbol separated values , you can take the derived column transformation and give the expression as

SalesOrg = dropRight(dropLeft(replace(replace(toString(SalesOrg),",","||"),'"',''),1),1), MainGtin = dropRight(dropLeft(replace(replace(toString(MainGtin),",","||"),'"',''),1),1)

The above expression will remove the unwanted characters and give as the pipe delimited values.