API Json to Oracle table : Extension

Question

Hi,


It is an extension of the question "API Json to Oracle table"

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

I am using Data flow and reading the API in Source and oracle in Sink.

I need suggestion on writing the Mapping. In my earlier question, I am able to extract some of the part but not the columns:-
VendorArticleNo
FromCut
DisplayUnitOfMeasure
StatusVendor

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"
						}
					]
				},
				"to_ArticleNumber": {
					"results": [
						{
							"__metadata": {
								"id": "https://billaxt.eu/data('3152097')"
							},
							"VendorArticleNo": "3152097",
							"To_ArticleNumberPorg": {
								"results": [
									{
										"__metadata": {
											"id": "https://billfgh.eu/data('X01')"
										},
										"FromCut": "",
										"DisplayUnitOfMeasure": "X01",
										"StatusVendor": "A"
									},
									{
										"__metadata": {
											"id": "https://billghu.eu/data('x02')"
										},
										"FromCut": "",
										"DisplayUnitOfMeasure": "X02",
										"StatusVendor": "B"
									}
								]
							}
						}
					]
				}
			}
		]
	}
}


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

Productno  FixedPrice   	SalesOrg   			MainGtin 
74         	120				01||11||02||12		01||4126||02||4125										VendorArticleNo       FromCut              DisplayUnitOfMeasure       StatusVendor
 3152097			01||NULL||02||NULL		01||X01||02||X02	     1||A||02||B			    		

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

Answer 1

Use the Copy Data or Data Flow activity to read data from the API.

You will need to flatten the JSON structure, especially the nested arrays like to_GtinSalesOrg and to_ArticleNumber. This can be done using the 'Flatten' transformation in a Data Flow.

For fields like SalesOrg and MainGtin, you will need to concatenate the values from the nested arrays along with sequence numbers.

Use a 'Derived Column' transformation to create these concatenated strings. You might need to use a combination of string functions and expressions to achieve the correct format.

To assign sequence numbers to each item in the arrays, this could be done using a script transformation or a derived column with appropriate expressions.

For null values in fields like FromCut, ensure that they are represented as 'NULL' in the concatenated string.

Map each derived field to the corresponding column in your Oracle table and ensure that the data types and formats match your Oracle table's schema.

In the sink settings of your Data Flow, specify your Oracle database and table then map the output columns of the Data Flow to the corresponding columns in the Oracle table.

Here is an example expression for concatenating SalesOrg and MainGtin:

concat('01||', first(SalesOrg), '||02||', second(SalesOrg), '||', '01||', first(MainGtin), '||02||', second(MainGtin))