topic Re: Flatten nested JSON in KQL DB in Eventstream

Flatten nested JSON in KQL DB

Debasish_p — Mon, 20 May 2024 20:58:44 GMT

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.

Re: Flatten nested JSON in KQL DB

Anonymous — Tue, 16 Jan 2024 11:19:11 GMT

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?

Re: Flatten nested JSON in KQL DB

Debasish_p — Tue, 16 Jan 2024 11:36:35 GMT

Thanks Anonymous . 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" }

]

}

]

Re: Flatten nested JSON in KQL DB

HimanshuS-msft — Tue, 16 Jan 2024 23:06:03 GMT

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

Re: Flatten nested JSON in KQL DB

Anonymous — Thu, 18 Jan 2024 10:44:33 GMT

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 .

Re: Flatten nested JSON in KQL DB

Debasish_p — Thu, 18 Jan 2024 11:44:27 GMT

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

Re: Flatten nested JSON in KQL DB

Anonymous — Thu, 25 Jan 2024 10:47:38 GMT

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.
.

Re: Flatten nested JSON in KQL DB

frithjof_v — Thu, 25 Jan 2024 17:41:25 GMT

Did you try the "Event processing before ingestion"?

https://learn.microsoft.com/en-us/fabric/real-time-analytics/event-streams/add-manage-eventstream-destinations#add-a-kql-database-as-a-destination

Re: Flatten nested JSON in KQL DB

Anonymous — Mon, 29 Jan 2024 09:47:47 GMT

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.

Re: Flatten nested JSON in KQL DB

Debasish_p — Wed, 06 Mar 2024 07:23:19 GMT

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

Re: Flatten nested JSON in KQL DB

Anonymous — Wed, 06 Mar 2024 07:32:42 GMT

Hi Debasish_p ,

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