ADF source API value reformatting issue

Question

I am trying to load data from Business Central API to json file. but the API Response is not in correct format BC API Response is coming as below

 "@odata.context": "https://www125.bcdev.sbp.eyclienthub.com:7048/BC183-W1-27480-ACS-DEV/api/gfo/datasets/v1.0/$metadata#Edm.String",
    "value": "[{\"companyName\":\"0643 CRONUS COPY 13 Ltd.\",\"companyId\":\"{2C49691B-24BB-4525-A5C8-81ECCBB73D8D}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"0712 CRONUS COPY 3 Ltd.\",\"companyId\":\"{32B0784C-9AE7-452A-A30E-D0649C95D611}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"0810 CRONUS COPY 12 Ltd.\",\"companyId\":\"{EE9E2B33-D974-4514-92F4-79F1697A22DA}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"0830 CRONUS COPY 4 Ltd.\",\"companyId\":\"{BA218139-5CC1-4A62-A820-C1E35D37E82D}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"0887 CRONUS COPY 11 Ltd.\",\"companyId\":\"{7E08B3F0-CCFB-45C8-8475-EC417B810AA7}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"1144 CRONUS Copy 1 Ltd.\",\"companyId\":\"{BB27EC23-2C38-474F-8810-BAF6C8324979}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"1172 CRONUS COPY 2 Ltd.\",\"companyId\":\"{CF6C44F8-8A84-4B95-AD6E-F89264FF2E85}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"1542 CRONUS COPY 10 Ltd.\",\"companyId\":\"{E7BC4D36-FC4E-4CF1-AD1E-C9602E486F0F}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"1679 CRONUS COPY 14 Ltd.\",\"companyId\":\"{6E988926-9218-49C9-B29F-BF017726CA1A}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS COPY 0 Ltd.\",\"companyId\":\"{63FC60CE-1314-4A2B-A086-A153CB7A7C36}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS COPY 5 Ltd.\",\"companyId\":\"{B560F62E-6733-4A0E-8C97-02EFDFE62CB7}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS COPY 6 Ltd.\",\"companyId\":\"{0A231CFB-439D-4DEA-80BC-FE2A1125B770}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS COPY 7 Ltd.\",\"companyId\":\"{D468B4AB-79F7-4CBD-86DD-6E59AE072CEC}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS COPY 8 Ltd.\",\"companyId\":\"{0A16319A-1BCE-4951-8698-631C0AE188CB}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS COPY 9 Ltd.\",\"companyId\":\"{85FFF083-5FB9-4D6F-87B4-495B40B198AD}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS International Ltd.\",\"companyId\":\"{A4DC874A-9F2B-42CE-8C94-CBE26D8643C0}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS TEST A Ltd.\",\"companyId\":\"{49859DE1-251B-4865-BA07-AA9E3A7FC25D}\",\"recordsUpdated\":true,\"recordsDeleted\":false},{\"companyName\":\"CRONUS TEST B Ltd.\",\"companyId\":\"{C0234BAC-D5FB-40A9-B6FC-F24697BC4479}\",\"recordsUpdated\":true,\"recordsDeleted\":false}]"

but I want the output to come as below

[
	{
		"companyName": "0643 CRONUS COPY 13 Ltd.",
		"companyId": "{2C49691B-24BB-4525-A5C8-81ECCBB73D8D}",
		"recordsUpdated": true,
		"recordsDeleted": false
	},
	{
		"companyName": "0712 CRONUS COPY 3 Ltd.",
		"companyId": "{32B0784C-9AE7-452A-A30E-D0649C95D611}",
		"recordsUpdated": true,
		"recordsDeleted": false
	},
	{
		"companyName": "0810 CRONUS COPY 12 Ltd.",
		"companyId": "{EE9E2B33-D974-4514-92F4-79F1697A22DA}",
		"recordsUpdated": true,
		"recordsDeleted": false
	},
	{
		"companyName": "0830 CRONUS COPY 4 Ltd.",
		"companyId": "{BA218139-5CC1-4A62-A820-C1E35D37E82D}",
		"recordsUpdated": true,
		"recordsDeleted": false
	},
	{
		"companyName": "0887 CRONUS COPY 11 Ltd.",
		"companyId": "{7E08B3F0-CCFB-45C8-8475-EC417B810AA7}",
		"recordsUpdated": true,
		"recordsDeleted": false
	},
	{
		"companyName": "1144 CRONUS Copy 1 Ltd.",
		"companyId": "{BB27EC23-2C38-474F-8810-BAF6C8324979}",
		"recordsUpdated": true,
		"recordsDeleted": false
	}
]

(In the Output Response \” to be replaced with ” to get the required format) How can I achieve this using ADF control flow. Or if there any other way by using Database if possible please suggest

Answer 1

Hi @Arundhati Sen

My understanding is that the response which you are getting back is of the form of string and not JSON and since you have this issue. I did a small test with
https://services.odata.org/TripPinRESTierService/(S(eat13bkzh4wlwrh2tx4nxhfb))/People

and I see the data to be fine .

I suggest you to please try out the replace function.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expressions-usage#replace

Thanks

Himanshu