Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Debasish_p
Frequent Visitor

Flatten nested JSON in KQL DB

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.

1 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

View solution in original post

10 REPLIES 10
frithjof_v
Continued Contributor
Continued Contributor

HimanshuS-msft
Community Support
Community Support

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 " 

 

HimanshuSmsft_0-1705446224674.png

Json mapping 

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

Thanks
HImanshu

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

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

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.

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

Hi @Debasish_p ,

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

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
Frequent Visitor

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
Community Support
Community Support

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

vgchennamsft_0-1705403944895.png

 



Did you got a chance to refer above document?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayFabricCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Kudoed Authors