Share via

How to copy a single object to csv from a json file having a single array and multiple objects ?

Amar Agnihotri 926 Reputation points
Dec 29, 2022, 3:09 PM

Hi,
I am having this json file

274847-image.png

It is containing only single array "issues"as shown
274759-image.png
I am trying to flatten this json but want to keep only "names" object in csv file because i want to save the key values of "names" object in csv . The below one -

274882-image.png
and i want it to be save in csv as

274876-image.png

Can anyone suggest some way to achieve this. I tried using flatten activity in dataflow but flatten activity needs an Array to pass into that but i don't need an array values

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,259 questions
{count} votes

Accepted answer
  1. MartinJaffer-MSFT 26,226 Reputation points
    Dec 30, 2022, 9:18 PM

    Yes, it is unpivot, @Amar Agnihotri , with a couple extra steps:

    First a select to grab all and only the columns starting with "customfield_"

    first_select

    use the double-chevron to expand a rule-based mapping so we can specify to look under "names".
    For a condition, use

    startsWith(name, "customfield_")  
    

    Also, pick one other column, outside of names. The unpivot wants something not part of the pivot. I don't think it matters what you choose. This will be used in the Unpivot's "Ungroup by" column.

    Unpivot key

    The Unpivot Key is the name for your Column A in desired output. It gets populated with all the "customfield_123" when you choose "Pick column names as values".

    and lastly of the Unpivoted Columns, is your Column B in desired output.

    Unpivoted columns

    Now this results in 3 columns, one of which we don't care about.

    results

    So after the Unpivot, add a Select transformation to cut it out.

    cleanup

    DataFlow script

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Amar Agnihotri 926 Reputation points
    Jan 4, 2023, 1:48 PM

    Hi,
    @MartinJaffer-MSFT
    Thanks for the help . It's done now :)

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.