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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Impactful Individual
Impactful Individual

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
FabricCarousel_June2024

Fabric Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors