Using HierarchicalTranslator in Fabric copy activity

Hi shivangjha ,

You're hitting a common limitation with Fabric copy activities when trying to create nested JSON structures. The dot notation approach you tried creates literal field names instead of hierarchical structures.

Here's what actually works:

Option 1: Use Data Flow instead Copy activity isn't great for complex JSON transformations. Switch to Data Flow:

• Add your lakehouse table as source
• Use Derived Column transformation:
   

  parameters = @(b=b, c=c, d=d)

• Select only a and parameters columns
• Send to MongoDB sink

Option 2: Pre-build the structure in your lakehouse Create a view or use SQL to structure your data first:

SELECT 
    a,
    STRUCT(b as b, c as c, d as d) as parameters
FROM table1

Option 3: JSON string approach (last resort) If you must stick with copy activity, create the nested object as a JSON string:

parameters = concat('{"b":"', b, '","c":"', c, '","d":"', d, '"}')

The HierarchicalTranslator reverting to Tabular is expected behavior - it's not fully supported for MongoDB sinks in Fabric yet.

Data Flow is your best bet for reliable hierarchical JSON creation. Have you tried that approach yet?

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.