Forum Discussion

mariussve1's avatar
mariussve1
Solution Sage
2 years ago

Having issue mapping json arrays

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
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mariussve1 

     

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

     

    Regards,

    Nono Chen

    • mariussve1's avatar
      mariussve1
      Solution Sage
      2 years ago

      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