How to process this JSON structure

Question

Hi, i get this structure back from a web activity. Showing only a part

{ "Response": "[\r\n {\r\n "CreationDate": "2024-01-07T00:00:00Z",\r\n "Operation": "ViewReport",\r\n "UserId": "William3",\r\n "NoEvents": 1,\r\n "ReportId": "F7428508-3173-4B86-8131-C84F1B166928",\r\n "WorkspaceId": "C028AD16-23F9-4F56-9CD7-68C07E95EBCF",\r\n "DashboardId": null\r\n },\r\n {\r\n "CreationDate": "2024-01-07T00:00:00Z",\r\n "Operation": "ViewReport",\r\n "UserId": "John1",\r\n "NoEvents": 1,\r\n "ReportId": "08DEF445-F64C-4477-A0DD-2BEE8E9A3E31",\r\n "WorkspaceId": "A65798B2-0FFD-473C-9CEE-E83D9EB92B32",\r\n "DashboardId": null\r\n },\r\n {\r\n "CreationDate": "2024-01-07T00:00:00Z",\r\n "Operation": "ViewRepor

Multiple rows and each row has multiple attributes, starting with Creation Date and ending with Dashboard. In the end I want to insert each row in a SQL server table.
I have been trying with a ForEach activity , but I can't get it working

When I am using this "items": "@activity('wa_get_workspace_id').output", it says:
The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'.

Tried other options as well but nothing works.

Anyone any idea?

regards
Ron

Answer 1

Hello Poel van der, RE (Ron),

It seems like you're trying to process JSON data and insert it into a SQL table using a ForEach activity.

The error message you're receiving indicates that the length function is expecting an array or a string, but it's receiving an object instead. This typically means that the data structure you're working with is not in the expected format.

To resolve this issue, you'll need to ensure that the data passed to the items property in the ForEach activity is an array.

If @activity('wa_get_workspace_id').output is supposed to return an array but is returning an object, you might need to access the specific property of the output that contains the array.

Ex:

"items": "@activity('wa_get_workspace_id').output.Response"

If the Response property itself is a JSON string that represents an array, you'll need to parse it first before you can use it in the ForEach activity:

"items": "@json(activity('wa_get_workspace_id').output.Response)"

Once you have the array, you can iterate over each item and use a Copy activity within the ForEach loop to insert the data into your SQL table.

I hope this helps. Please let me know if you have any further questions.