Forum Discussion

Debasish_p's avatar
Debasish_p
Frequent Visitor
2 years ago
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.

Eventstream
Real-time Analytics
  • Debasish_p's avatar
    Debasish_p
    2 years ago

    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

10 Replies

  • Debasish_p's avatar
    Debasish_p
    Frequent Visitor
    2 years ago

    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" }
    ]
    }
    ]
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
      Debasish_p
      Frequent Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

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