Getting "Malformed records are detected in schema inference" when trying to flatten JSON using a data flow in ADF

Question

Using Azure Data Factory, I've copied data from an open-source API that requires no authorisation (https://directory.spineservices.nhs.uk/ORD/2-0-0/organisations/rxl) and put into into an Azure blob storage account as a JSON file. Now I'm trying to flatten some columns in a data flow but I'm having issues with my source activity. When I try to preview the data, I'm getting this error:

Malformed records are detected in schema inference. Parse Mode: FAILFAST. It could be because of a wrong selection in document form to parse json file(s). Please try a different 'Document form' (Single document/Document per line/Array of documents) on the json source.

I have tried changing the 'Document form' option to every option and I'm still getting the same error.

I can preview the data ok when looking at the dataset. But I can't look at it in the data flow... Any help appreciated!

Thanks.

Here is a sample of the data:

[
    {
        "Organisation": {
            "Rels": {
                "Rel": [
                    {
                        "Date": [
                            {
                                "Start": "2020-04-01",
                                "Type": "Operational"
                            }
                        ],
                        "id": "RE5",
                        "Status": "Active",
                        "Target": {
                            "OrgId": {
                                "assigningAuthorityName": "HSCIC",
                                "extension": "QE1",
                                "root": "2.16.840.1.113883.2.1.3.2.4.18.48"
                            },
                            "PrimaryRoleId": {
                                "id": "RO261",
                                "uniqueRoleId": 300734
                            }
                        },
                        "uniqueRelId": 666658
                    },
                    {
                        "Date": [
                            {
                                "End": "2020-03-31",
                                "Start": "2016-04-01",
                                "Type": "Operational"
                            }
                        ],
                        "id": "RE5",
                        "Status": "Inactive",
                        "Target": {
                            "OrgId": {
                                "assigningAuthorityName": "HSCIC",
                                "extension": "Q84",
                                "root": "2.16.840.1.113883.2.1.3.2.4.18.48"
                            },
                            "PrimaryRoleId": {
                                "id": "RO210",
                                "uniqueRoleId": 278955
                            }
                        },
                        "uniqueRelId": 464825
                    }
...

Answer 1

• I got a similar error when I try to copy the file to blob storage as JSON file and then try to read it in dataflow (while importing projection)

• In copy data activity sink settings, using file pattern as Array of objects is causing this error to occur (dataset preview is working fine same as in your case). Instead, set this to file pattern as Set of Objects.

• Now reading this file after importing projection would give the following schema using JSON setting as Document per line:

• The Dataflow's data preview would look as shown in the below image:

NOTE: Instead of copying the response to a JSON file, use REST linked service to access this open-source API to directly read the data. However the data preview would be slightly different from what you see above.