Forum Discussion
Solved
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.
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
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" }
]
}
]