Any changes happened in the backend of Parse JSON Mapping data flow in ADF in Feb Week 2

Piyush Jain 0 Reputation points

Hi All,

Is anyone else faced an issue with Json array not being parsed correctly in the ADF Mapping data flows. Earlier the string worked fined and able to parse the columns but it now shows Null

Json Array is like

[{"abc":"def", "xyz":["bcd", "ahdjwhg"],"uvw":["abc, def"]}]

  1. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi Piyush Jain

    This behavior is generally not due to any backend change in ADF, but is usually related to how the JSON is being interpreted in the data flow.

    From what you’ve shared, the JSON is a root-level array:

    JSON

    [{"abc":"def", "xyz":["bcd","ahdjwhg"], "uvw":["abc, def"]}]

    Show more lines

    In such cases, the most common reason for columns suddenly showing as NULL is the JSON source configuration, especially the Document form setting. If this is not set correctly (for example, set to “Single document” instead of “Array of documents”), ADF may fail to infer the schema properly and return null values.

    Another point to check is the source projection/schema. Mapping Data Flows rely on sampled data to infer schema, and if the schema is not imported properly or has changed slightly, some fields may not be picked up correctly and can appear as null.

    Also, if this JSON is coming as a string (for example from a CSV or another column), the commas inside the value like "abc, def" can break parsing unless the column is properly formatted, which can again result in null outputs. [learn.microsoft.com]

    To address the issue, please validate the JSON source settings and ensure the Document form is set to “Array of documents”, then re-import the projection and confirm the fields are detected correctly. If the JSON is stored in a string column, use a Parse transformation to convert it into structured fields, as JSON is a supported format. [learn.microsoft.com]

    For array fields such as xyz and uvw, they will not automatically expand into columns, so you will need to use a Flatten transformation to unroll them into usable rows or columns based on your requirement.

    You can refer to the following Microsoft documentation for more details:

    Overall, validating the JSON source configuration (especially the document form) and schema projection should help resolve the issue.

  2. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi @Piyush Jain
    Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

  3. Piyush Jain 0 Reputation points

    Hi @Pilladi Padma Sai Manisha , Thanks for sharing the details.

    Could you please help me understand why the transformation, which had been working consistently for the past 3–4 months, has suddenly stopped functioning, even though there have been no schema or data changes in the source JSON?

    During the development phase, the single document option was not functioning, so we selected the array of documents approach, which worked properly and was tested multiple times over the last 3–4 months. Now, however, both the single document and array of documents options are failing.

    If the format was incorrect earlier, I am trying to understand why it worked reliably for several months. The concern is that, without any changes in the source data, column schema, or format, this transformation has stopped working with the same configurations in both Non-Prod and Prod environments.

    Your guidance on what might have caused this sudden change would be greatly appreciated.

  4. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi Piyush,

    Thanks for the clarification, I understand your concern, especially since there were no changes on your end.

    In such cases, this behavior is usually due to backend/runtime updates in ADF that may enforce stricter JSON parsing rules. Earlier, the same format might have been handled more leniently, which is why it worked for several months.

    Since both “single document” and “array of documents” are now failing, it indicates the current parsing logic is not aligning with the JSON structure during execution.

    I suggest validating the schema explicitly (instead of relying on inference) and checking the Data Flow debug/run output to see where it’s returning null.

    Let me know if you’d like me to review the configuration with you.

  5. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi Piyush,
    I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

Sign in to comment

1 answer

  1. AI answer

    ADF mapping data flows did not change behavior specifically in “Feb Week 2” in the documentation, but the described symptom (JSON array suddenly not parsing and returning null) matches known JSON/CSV and JSON parsing issues and configuration pitfalls.

    Based on the JSON sample:

    [{"abc":"def", "xyz":["bcd", "ahdjwhg"],"uvw":["abc, def"]}]

    there are two likely causes and corresponding fixes:

    1. Wrong JSON document form / parsing settings

    If the JSON source or Parse transformation is misconfigured, ADF can fail schema inference and return nulls.

    Relevant error pattern in ADF:

    • Error code DF-JSON-WrongDocumentForm or DF-Executor-SystemInvalidJson with message about malformed records or unsupported encoding/multiline.

    Recommended checks:

    1. In the JSON source of the data flow, open JSON settings and try different Document form values:
      • Single document
      • Document per line
      • Array of documents The documentation explicitly states that most parsing errors are caused by a wrong Document form selection.
    2. If the JSON is produced by a Copy activity, verify:
      • Encoding (UTF-8/16/32 with BOM) vs. data flow JSON source settings.
      • Whether Single document is enabled when the file actually contains an array or multiple documents.
    3. Use Data preview and Inspect in the data flow to confirm the inferred schema and that the array fields (xyz, uvw) are recognized as arrays.
    4. JSON embedded in CSV / CDM or misread as multiple columns

    If the JSON array is stored as a string inside a CSV or CDM column, commas inside the JSON can cause the value to be split into multiple columns, which then breaks parsing and yields null.

    Known behavior:

    • When a JSON array or object is stored in a CSV column, commas inside the JSON are treated as column separators unless the value is properly quoted.
    • In CDM/CSV scenarios, ADF may infer one JSON attribute as multiple columns instead of a single JSON string column.

    Recommendation in this case:

    1. Ensure the JSON column is:
      • Double-quoted as a single CSV field.
      • Inner quotes escaped with a backslash (\). This ensures the entire JSON array is read as one string column.
    2. After the JSON is correctly ingested as a single string column, use a Parse transformation to convert it into a complex type (array/struct/map). The Parse transformation documentation recommends:
      • Use Detect type in the expression builder to infer the output schema from the JSON string.
      • Then use a Derived Column transformation to access nested elements.
    3. Special characters and attribute extraction issues

    If parsing complex types and extracting attributes (for example, using @model or keys with dashes), ADF can fail and return null. The documentation suggests:

    • For attributes like @model, convert the complex type to string, remove @ with replace, then parse again.
    • For JSON keys with -, use bracket notation and a variable: 
       var bestScore = data["best-score"];
 { bestScore : { "section 1": 1234 } }
    1. Snowflake / VARIANT scenarios (if sink is Snowflake)

    If the JSON is being written to Snowflake VARIANT columns and suddenly fails:

    • Snowflake VARIANT columns only accept struct/map/array types from data flows, not raw strings.
    • Use a Parse transformation before the Snowflake sink to convert the JSON string into array/struct/map.

    Summary of actions to fix null parsing:

    1. Verify JSON Document form in the JSON source and adjust (Single document / Document per line / Array of documents).
    2. If JSON is inside CSV/CDM:
      • Ensure the JSON value is fully double-quoted and inner quotes escaped.
      • Reimport or re-read the file so the JSON is a single string column.
    3. Use Parse transformation with Detect type to convert the JSON string into arrays/structs, then access elements via Derived Column.
    4. For special keys (@, -), apply the documented workarounds (string replace, bracket notation).

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer