Having issue mapping json arrays

mariussve1 — Mon, 08 Jul 2024 20:09:17 GMT

Hi all,

I'm having issue mapping json arrays in pipeline.

The source is an API and the sink is Fabric Warehouse.

This is the respons from API:

{ "tables": [ { "name": "PrimaryResult", "columns": [ { "name": "Date", "type": "string" }, { "name": "DateTime", "type": "datetime" }, { "name": "PowerBIWorkspaceId", "type": "string" }, { "name": "PowerBIWorkspaceName", "type": "string" }, { "name": "DatasetId", "type": "string" }, { "name": "ReportId", "type": "string" }, { "name": "VisualId", "type": "string" }, { "name": "ExecutingUser", "type": "string" }, { "name": "UserSession", "type": "string" }, { "name": "Sessions", "type": "long" } ], "rows": [ [ "2024-07-06", "2024-07-06T15:56:00Z", "GUID", "Workspace name", "GUID", "GUID", "GUID", "marius.sveen@", "GUID", 1 ] ] } ] }

When I push "Import schemas" it only returns this:

I cant find the collection referance when I choose my Fabric Warehouse as my destination. Can anyone please help?
I tried to edit the json manually like this:

"mappings": [ { "source": { "name": "$[0]", "type": "String" }, "sink": { "name": "Date", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[1]", "type": "String" }, "sink": { "name": "DateTime", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[2]", "type": "String" }, "sink": { "name": "PowerBIWorkspaceId", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[3]", "type": "String" }, "sink": { "name": "PowerBIWorkspaceName", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[4]", "type": "String" }, "sink": { "name": "DatasetId", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[5]", "type": "String" }, "sink": { "name": "ReportId", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[6]", "type": "String" }, "sink": { "name": "VisualId", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[7]", "type": "String" }, "sink": { "name": "ExecutingUser", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[8]", "type": "String" }, "sink": { "name": "UserSession", "physicalType": "varchar", "length": "8000" } }, { "source": { "name": "$[9]", "type": "String" }, "sink": { "name": "Sessions", "physicalType": "varchar", "length": "8000" } } ], "collectionReference": "$['tables'][0]['rows']", "mapComplexValuesToString": false, "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false }, "columnFlattenSettings": { "treatArrayAsString": false, "treatStructAsString": false, "flattenColumnDelimiter": "." }

But then it only write NULL

As you can see i tried to add:

"collectionReference": "$['tables'][0]['rows']",

But it does not work.

I have struggled with this for weeks, but cant find out how to fix it. Please help 🙂

Br
Marius

Re: Having issue mapping json arrays

Anonymous — Tue, 09 Jul 2024 01:31:42 GMT

Hi mariussve1

I found the following, and there are some methods and documentation mentioned in this post that may help you:

Solved: Copy Activity: JSON Array Mapping - Microsoft Fabric Community

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Having issue mapping json arrays

Anonymous — Tue, 16 Jul 2024 08:21:06 GMT

Hi mariussve1

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

Re: Having issue mapping json arrays

mariussve1 — Tue, 16 Jul 2024 08:45:27 GMT

Hi,

No, I still have the same issue. I'm not able to map this json array described in the orginal post. I tried to post a new comment in other topic as well, but have not recived any answers.

Br

Marius

topic Re: Having issue mapping json arrays in Pipelines

Having issue mapping json arrays

Re: Having issue mapping json arrays

Re: Having issue mapping json arrays

Re: Having issue mapping json arrays