ADF source API value reformatting issue

Arundhati Sen 151 Reputation points
2023-04-25T14:43:12.42+00:00

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 User's image

 "@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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,891 questions
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2023-04-26T21:39:25.05+00:00

    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 .

    User's image

    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

    0 comments No comments