Flatten nested JSON in KQL DB

[Debasish_p]

Hi All,

Is there a way to flatten and ingest nested JSON into KQL DB using Real time analytics. Consider it as a one time load from Onelake.

[frithjof_v]

Did you try the "Event processing before ingestion"?

https://learn.microsoft.com/en-us/fabric/real-time-analytics/event-streams/add-manage-eventstream-de...

[HimanshuS-msft]

Hello @Debasish_p 
Thanks for using the Fabric community.

I have not tested this scenario but I suggest that you can try the "ingestion mapping name " 

Json mapping 

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/json-mapping

Thanks
HImanshu

[Debasish_p]

Thanks @HimanshuS-msft , the above solution involves ADF. I was looking at flattening while directly ingesting in KQL DB.

[v-gchenna-msft]

Hi @Debasish_p ,

Apologies for the delay in reply from our side

I will request you to please go ahead with Microsoft support on this support-ticket-link.

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.
.

[v-gchenna-msft]

Hello @Debasish_p ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
If creted support ticket, please provide the ticket number as it would help us to track for more information.

[Debasish_p · Accepted solution]

Hi,

Hi All, the issue has been resolved. Below are the steps that I have done to resolve the issue.

1. Create 2 tables. One AS-IS(Staging) table and the otheris an target table

2. Create a KQL function and

3. adding it as an update policy in the target table.

Thanks

[v-gchenna-msft]

Hi @Debasish_p ,

Glad to know your query got resolved. Please continue using Fabric Community for your further queries.

[v-gchenna-msft]

Hi @Debasish_p ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .

[Debasish_p]

Thanks @v-gchenna-msft . I am able to ingest nested JSON to KQL. My question was more towards dynamically flattening a nested json before ingesting to KQL. 

You can take the sample data as below:

[

{

"id": "0001",

"type": "donut",

"name": "Cake",

"ppu": 0.55,

"batters":

[

{ "id": "1001", "type": "Regular" },

{ "id": "1002", "type": "Chocolate" },

{ "id": "1003", "type": "Blueberry" },

{ "id": "1004", "type": "Devil's Food" }

]

,

"topping":

[

{ "id": "5001", "type": "None" },

{ "id": "5002", "type": "Glazed" },

{ "id": "5005", "type": "Sugar" },

{ "id": "5007", "type": "Powdered Sugar" },

{ "id": "5006", "type": "Chocolate with Sprinkles" },

{ "id": "5003", "type": "Chocolate" },

{ "id": "5004", "type": "Maple" }

]

},

{

"id": "0002",

"type": "donut",

"name": "Raised",

"ppu": 0.55,

"batters":

[

{ "id": "1001", "type": "Regular" }

]

,

"topping":

[

{ "id": "5001", "type": "None" },

{ "id": "5002", "type": "Glazed" },

{ "id": "5005", "type": "Sugar" },

{ "id": "5003", "type": "Chocolate" },

{ "id": "5004", "type": "Maple" }

]

},

{

"id": "0003",

"type": "donut",

"name": "Old Fashioned",

"ppu": 0.55,

"batters":

[

{ "id": "1001", "type": "Regular" },

{ "id": "1002", "type": "Chocolate" }

]

,

"topping":

[

{ "id": "5001", "type": "None" },

{ "id": "5002", "type": "Glazed" },

{ "id": "5003", "type": "Chocolate" },

{ "id": "5004", "type": "Maple" }

]

}

]

[v-gchenna-msft]

Hi @Debasish_p ,

Thanks for using Fabric Community.
Can you please explan your query with more details?

We can insert Json data into KQL from One Lake -
Get data from OneLake - Microsoft Fabric | Microsoft Learn

Did you got a chance to refer above document?