Having issue mapping json arrays

[mariussve1]

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

[Anonymous]

Hi @mariussve1 

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

Regards,

Nono Chen

[mariussve1]

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

[Anonymous]

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.