Share via

Load each CSV row as a JSON string into a single column Azure SQL table with mapping data flow

WeirdMan 460 Reputation points
2026-04-16T17:49:03.38+00:00

I’m using mapping data flows and want to transform CSV data before writing it to Azure SQL database.

my CSV file in Azure Blob storage

The file contains multiple records, for example:

id,name,age
1,John,30
2,Jane,25

and I want to load data in Azure SQL table with a single column to store JSON

I want each CSV record to be inserted into Azure SQL as a separate row where the full CSV row is converted into a JSON object and stored in one column.

So the target table would contain values like:

{"id":1,"name":"John","age":30}
{"id":2,"name":"Jane","age":25}

Can this be done with ADF mapping data flow?

What is the best way to transform each row into JSON before loading it into Azure SQL?

Should I use a derived column or struct or another data flow expression for this?

Are there any limitations or performance concerns when storing row level JSON in Azure SQL?

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

0 comments

Answer accepted by question author

Amira Bedhiafi 42,046 Reputation points MVP Volunteer Moderator
2026-04-16T17:56:13.5433333+00:00

Hello !

Thank you for posting on MS Learn Q&A.

Yes this is possible but ADF mapping data flow is not the simplest way unless you truly need row by row transformation logic.

For your case, the cleanest way in is:

source (CSV) then build a complex object/map for each row then stringify then sink (Azure SQL single column)

Stringify transformation is specifically meant to turn complex types like structures, maps, or arrays into a single string value, which is exactly what you need before writing into one SQL column. Mapping data flow also supports functions like columnNames(), columns(), and keyValues() that can help build key/value objects dynamically.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-stringify

If your schema is fixed, use a derived column to create a structured column from the CSV fields, then pass that column through stringify and write the result to SQL.

For a dynamic schema, you can build a map from all incoming column names and values using columnNames() + columns() + keyValues(), then stringify that map. Those functions are documented in mapping data flow expressions.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expressions-usage

The main consideration is whether you actually need SQL to hold opaque JSON blobs rather than relational columns.

If you mostly just archive each source row as it is so storing JSON in one column is fine.

If you will filter, join, or report heavily on fields like age or name, relational columns are usually better for performance and maintenance.

If you do store JSON, Azure SQL supports indexing JSON-accessed properties via computed columns, and in newer SQL versions there is also JSON index support. https://learn.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver17

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-04-16T17:49:22.83+00:00

    Yes, this can be done with Azure Data Factory (or Synapse) mapping data flows, and the pattern is straightforward.

    1. Source
    • Use a CSV (Delimited text) dataset on Azure Blob Storage as a source in a mapping data flow.
    • The source will expose columns like id, name, age as separate fields.
    1. Build a JSON object per row in a Derived Column
    • Add a Derived Column transformation.
    • Create a new column, for example jsonRow, and build a complex structure (struct) or directly a JSON string.

    Option A – Create a complex column and stringify it (recommended)

    • In Derived Column, create a complex column using the expression builder UX:
      • Add a new column jsonStruct.
      • Use the UX to add subcolumns: id, name, age, mapping them to the incoming columns.
      • This makes jsonStruct a complex type.
    • Then add a Stringify transformation:
      • Column: jsonRow (new column to hold the JSON string).
      • Expression: select jsonStruct as the source complex field.
      • Set format: 'json' in the stringify expression, similar to: 
            stringify(jsonRow = jsonStruct ? string, format: 'json')
      • This produces a JSON string per row, like {"id":1,"name":"John","age":30}.

    This follows the documented pattern of using the stringify transformation to turn complex data types into JSON strings.

    Option B – Build JSON string directly in Derived Column

    • In Derived Column, define jsonRow as a string expression that concatenates fields into JSON text.
    • This is more error‑prone and less flexible than using a complex type + stringify.
    1. Sink to Azure SQL with a single JSON column
    • In the Sink transformation, point to the Azure SQL table that has a single column, e.g. Data nvarchar(max).
    • In the mapping, map jsonRow (string) to that single nvarchar(max) column.
    • JSON is textual, so storing it in nvarchar(max) is supported and documented for SQL Server/Azure SQL.

    From the JSON-in-SQL guidance:

    • JSON is stored as nvarchar(max).
    • JSON can be inserted via standard T‑SQL, and any client-side language that works with strings works with JSON.
    1. Limitations and performance considerations
    • Storage type: In Azure SQL/SQL Server, JSON is stored as nvarchar text. There is no native JSON type in older versions; in supported platforms a native json type can be used, but nvarchar(max) remains valid.
    • Indexing: To query JSON efficiently, typically use computed columns with JSON functions (e.g., JSON_VALUE) and index those columns. Storing entire rows as JSON is fine, but querying by JSON properties without such indexing can be slower.
    • Size: Ensure the target column length (nvarchar(max) or sufficiently large nvarchar(n)) can hold the full JSON row. Otherwise, inserts may fail with truncation errors.
    • Volume: For large volumes, row-level JSON is still just string data. Performance considerations are mainly around query patterns and indexing, not the act of storing the JSON.
    1. Summary flow
    • Source: CSV from Blob.
    • Derived Column: build complex struct from columns.
    • Stringify: convert struct to JSON string (format: 'json').
    • Sink: Azure SQL table with a single nvarchar(max) column mapped from the JSON string column.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.