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
adamcodes716
Regular Visitor

Trying to expand a json array into multiple columns

I am trying to set up an ingestion from an event hub to a KQL database.  The difficulty in doing this is that there is a payload field that is a few levels down.  I am trying to do this via the UI using an Event Processor to do this and I can go most of the way there.  I can get to the point where I have a column with the payload:

 

Here is a simple example of that column:  [{"name":"itemA", "section":"34", "key":"1234"}]

 

I could use "expand" here, but this breaks out the payload into 3 rows.  The problem with this is that the "key" only exists in one of the rows and I have no way to tie it back to the other entries.  Is there another option using Event Processor?  I am trying to avoid doing this in code.

1 ACCEPTED SOLUTION
v-gchenna-msft
Community Support
Community Support

Hi @adamcodes716,

Thanks for using Fabric Community.
You will need to do this KQL Queryset with 2 lines of code: mv-expand operator and bag_unpack() in KQL language. You can put these inside a function and call this function from Update Policies to transform this data on the fly.

Docs to refer -

mv-expand operator - Azure Data Explorer & Real-Time Analytics | Microsoft Learn
bag_unpack plugin - Azure Data Explorer & Real-Time Analytics | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.

View solution in original post

4 REPLIES 4
v-gchenna-msft
Community Support
Community Support

Hi @adamcodes716,

Thanks for using Fabric Community.
You will need to do this KQL Queryset with 2 lines of code: mv-expand operator and bag_unpack() in KQL language. You can put these inside a function and call this function from Update Policies to transform this data on the fly.

Docs to refer -

mv-expand operator - Azure Data Explorer & Real-Time Analytics | Microsoft Learn
bag_unpack plugin - Azure Data Explorer & Real-Time Analytics | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.

Hi @adamcodes716 ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thanks

Hi @adamcodes716 ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thanks

Hi @adamcodes716 ,

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

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