How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow?
How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow? Hi Community, I'm trying to extract and load data from API returning the following JSON format into an Azure SQL table using Azure Data Factory. { "2023-07-30": [], "2023-07-31": [], "2023-08-01": [ { "breakdown": "email", "contacts": 2, "customers": 2 } ], "2023-08-02": [], "2023-08-03": [ { "breakdown": "direct", "contacts": 5, "customers": 1 }, { "breakdown": "referral", "contacts": 3, "customers": 0 } ], "2023-08-04": [], "2023-09-01": [ { "breakdown": "direct", "contacts": 76, "customers": 40 } ], "2023-09-02": [], "2023-09-03": [] } Goal: I want to flatten this nested structure and load it into Azure SQL like this: Expand table ReportDate Breakdown Contacts Customers 2023-07-30 (no row) (no row) (no row) 2023-07-31 (no row) (no row) (no row) 2023-08-01 email 2 2 2023-08-02 (no row) (no row) (no row) 2023-08-03 direct 5 1 2023-08-03 referral 3 0 2023-08-04 (no row) (no row) (no row) 2023-09-01 direct 76 40 2023-09-02 (no row) (no row) (no row) 2023-09-03 (no row) (no row) (no row)
Data transformation using PostgreSQL connection
Hi Team Currently, I am working on a project where I need to incrementally load one table data from Schema A of Azure postgreSQL database to schema B. The table i am using have 100+ columns and few of them are numeric and whenever i am trying to read data from source table using data flows its generating Null values for all numeric columns. See following screenshot for reference. Source: Destination: If you notice, the last row contains data for numeric data types as well. To ensure ADF reads numeric and decimal values correctly, I clicked on the "overwrite schema" button in the data flow source and reselected the same decimal data type, as ADF converts numeric data types to decimal. I suspect this might be a bug in ADF. Could you please let me know where I can report this issue? Additionally, is there a better way to resolve it? Thanks, Dinesh
Alter Row Ignoring its Conditions
Hello. I have an ADF Dataflow which has two sources, a blob container with JSON files and an Azure SQL table. The sink is the same SQL table as the SQL source, the idea being to conditionally insert new rows, update rows with a later modified date in the JSON source or do nothing if the ID exists in the SQL table with the same modified date. In the Dataflow I join the rows on id, which is unique in both sources, and then use an Alter row action to insert if the id column from the SQL source is null, update if it's not null but the last updated timestamp in the JSON source is newer, or delete if the last updated timestamp in the JSON source is the same or older (delete is not permitted in the sink settings so that should ignore/do nothing). The problem I'm having is I get a primary key violation error when running the Dataflow as it's trying to insert rows that already exist: For example in my run history (160806 is the minimum value for ID in the SQL database): So for troubleshooting I put a filter directly after each source for that ticket ID so when I'm debugging I only see that single row. Now here is the configuration of my Alter row action: It should insert only if the SQLTickets id column is null, but here in the data preview from the same Alter rows action. It's marked as an insert, despite the id column from both sources clearly having a value: However, when I do a data preview in the expression builder itself, it correctly evaluates to false: I'm so confused. I've used this technique in other Dataflows without any issues so I really have no idea what's going on here. I've been troubleshooting it for days without any result. I've even tried putting a filter after the Alter row action to explicitly filter out rows where the SQL id column is not null and the timestamps are the same. The data preview shows them filtered out but yet it still tries to insert the rows it should be ignoring or updating anyway when I do a test run. What am I doing wrong here?
Json data structure reconstruction from array.
Hi Everyone, I am working on an Azure Cosmos DB migration where I need to transform the structure of documents in the "Notes" container. While I have identified most of the migration steps, I am currently facing a challenge with structural conversion and parsing of a JSON array field inside Azure Data Factory (ADF) Data Flow. Problem Context: I need to convert the "NoteDetails" array field from the original document format to a new normalized structure. The transformation involves flattening the array and restructuring it into a key-value format within a new field called "NormalizedNoteDetails". Original Document (Current Cosmos DB Structure): { "id": "123", "ownerId" : "1234", "noteCategory" : "somecategory", "NoteDetails": [ { "Key": "PropertyNameKey1", "Value": ["prop1of1", "prop2of1"] }, { "Key": "PropertyNameKey2", "Value": ["prop1of2", "prop2of2"] }] } Expected Transformed Structure (Target Format in Cosmos DB): { "id": "123", "ownerId" : "1234", "noteCategory" : "somecategory", "normalizedNoteDetails": { "PropertyNameKey1": ["prop1of1", "prop2of1"], "PropertyNameKey2": ["prop1of2", "prop2of2"] } } Specific Question: How can I efficiently parse and transform the "Notes" JSON array field inside an ADF Data Flow activity to achieve the new structure? I have tried flatten and pivot with no success but I am newbie to the ADF. A concrete example would help me tremendously. Thanks.
How do I unpivot with schema drift enabled?
I have a source without a pre-defined schema. I derive each column name using a column pattern expression: Data preview shows what I expect (which is a file in a blob container): I then have a Select step that selects each column and renames 'Marker name' to 'Marker_name22': Data preview again shows what I expect (same columns with 'Marker name' renamed). Now in the unpivot step, I would like to ungroup by the 'Marker_name22' column and unpivot all other columns, but the 'Marker_name22' column is not available: I am unsure how to proceed from here. Thanks in advance for the help.Columns from D365 CRM not found
Hello, I want to copy data from D365 CRM Opportunities into an Azure SQL table on a schedule. When I connect to our company.crm.dynamics.com using SSMS, I am able to do a SELECT DISTINCT statecode, statecodename FROM opportunity and get results for both columns. Other out-of-box columns follow the same pattern where there is a xxxcode and a xxxxcodename column pairs. However, in ADF when I have D365 CRM Opportunities as a source, and the Azure SQL table as a sink, and I go to verify mapping, statecode is found but statecodename is not, and many other 'xxxxxxxxname' columns are not found either. Why is that?Action Required: Switch from Memory Optimized Data Flows in Azure Data Factory to General Purpose
Azure Data Factory Memory Optimized Data Flows will be fully retired on April 1, 2027. Going forward, all ADF Data Flows will use the General Purpose SKU that will provide performance that is superior to the current Memory Optimized and at the General-Purpose price.
Complex JSON from a REST Api with Dataflow
Hi. I have a Rest API that retreives a complex json. In order to flatten that. Do i have to store de json in a file first? or can i flateen that json from the Rest API directly? Do you know if is it an examle? (i found videos of flatten complex json from a json file, but not directly from a Rest API) I get this error trying test connection This api works properly in a copy_data task Thank you
