web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Creating JSON with nes...
Power Automate
Unanswered

Creating JSON with nested array from CSV. No Idea How!!!

(0) ShareShare
ReportReport
Posted on by chlxco 8

hi all,

 

Im in the following pickle. I have my csv data as follows (fictional data 😊 😞

FirstNameLastNameClassTeacher
JohnDoeMathCarlos
JohnDoeEnglishAnna
JohnDoeArtKim
SarahMillerMathKelly
SarahMillerArtMatt

 

I dont have problems to consume the CSV from either OneDrive, Sharepoint etc. However, Once loaded, I'm having several issues to create the following JSON structure. End goal is use that content as a payload in my API.  (I dont have API call issues either, thats tested already). Ive watched tons of examples where people create flat Jason structures but nothing like this. Any idea, support, guidance? Really appreciate it.

 

Expected Structure:

[
{
"FirstName":"John",
"LastName":"Doe",
"Schedule":[ {"Class":"Math","Teacher":"Carlos"},{"Class":"English","Teacher":"Anna"},{"Class":"Art","Teacher":"Kim"}]
},
{
"FirstName":"Sarah",
"LastName":"Miller",
"Schedule":[ {"Class":"Math","Teacher":"Kelly"},{"Class":"Art","Teacher":"Matt"}]
}
]
Categories:
Building flows
I have the same question (0)
  • devtollon Profile Picture
    devtollon 24 on at

    Hi @chlxco ,

     

    Power Automate, as far as I know, does not have a built-in way to parse a csv file. You'll need to do it by hand in order to prepare your data to be stored inside a JSON with the structure you want.

     

    I leave you this article which shows an example on how to do that: https://www.tachytelic.net/2021/02/power-automate-parse-csv/ 

     

    Once you've it done, to create your JSON structure, what I would do is:

     

    1. Create a variable (type object) to store the entire JSON;
    2. Create a variable (type array) to store the entire array of objects;
    3. Create a variable (type array) to store the Schedule objects;
    4. Create a variable (type object) to store the single Schedule object;

    Once you have this structure, you can loop though the CSV extracted values and compose the JSON with your kind of structure.

     

    Let me know if this solved your issue!

  • Matthy79 Profile Picture
    Matthy79 4,180 Super User 2024 Season 1 on at

    Hello @chlxco,

     

    As @devtollon already mentioned you need to read the csv on your own or you use a third party connector. As for the transformation you don't need variables or loops. This can also be done by xpath. Here is a sample of how it would work:

     

    {"id":"2f3e2d07-7aa3-4896-96e8-112d0c004f41","brandColor":"#8C3900","connectionReferences":{"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}},"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_csv_array_converter","operationDefinition":{"type":"Scope","actions":{"Compose_converted_csv":{"type":"Compose","inputs":[{"FirstName":"John","LastName":"Doe","Class":"Math","Teacher":"Carlos"},{"FirstName":"John","LastName":"Doe","Class":"English","Teacher":"Anna"},{"FirstName":"John","LastName":"Doe","Class":"Art","Teacher":"Kim"},{"FirstName":"Sarah","LastName":"Miller","Class":"Math","Teacher":"Kelly"},{"FirstName":"Sarah","LastName":"Miller","Class":"Art","Teacher":"Matt"}],"runAfter":{},"metadata":{"operationMetadataId":"93bb91cd-6bbc-4985-bee7-6000e99181f6"}},"Select_add_schedule":{"type":"Select","inputs":{"from":"@outputs('Compose_converted_csv')","select":"@addProperty(item(), 'schedule', concat('{\"Class\":\"', item()['Class'], '\",\"Teacher\":\"', item()['Teacher'], '\"}'))"},"runAfter":{"Select_names":["Succeeded"]},"metadata":{"operationMetadataId":"cb2bb348-0d88-4fd9-a7a8-1dccd5b9c310"}},"Compose_xml":{"type":"Compose","inputs":"@xml(json(concat('{ \"root\": { \"user\": ', body('Select_add_schedule'), ' }}')))","runAfter":{"Select_add_schedule":["Succeeded"]},"metadata":{"operationMetadataId":"29d08333-b964-43ef-9c21-42ef9c5ebafb"}},"Select_api_payload":{"type":"Select","inputs":{"from":"@union(body('Select_names'),body('Select_names'))","select":{"FirstName":"@item()['FirstName']","LastName":"@item()['LastName']","Schedule":"@json(concat('[', join(xpath(outputs('Compose_xml'), concat('root/user[FirstName=\"', item()['FirstName'] ,'\" and LastName=\"', item()['LastName'] ,'\"]/schedule/text()')), ','), ']'))"}},"runAfter":{"Compose_xml":["Succeeded"]},"metadata":{"operationMetadataId":"96eb86b0-2873-4e87-b083-4ec11bb6df7e"}},"Select_names":{"type":"Select","inputs":{"from":"@outputs('Compose_converted_csv')","select":{"FirstName":"@item()['FirstName']","LastName":"@item()['LastName']"}},"runAfter":{"Compose_converted_csv":["Succeeded"]},"metadata":{"operationMetadataId":"a417abd8-8ad4-4598-a230-eea5bcb1e41e"}}},"runAfter":{},"metadata":{"operationMetadataId":"4976f52b-8aba-453b-8132-9bf875c42167"}}}

    You can add this by using the clipboard in the old designer.

     

    If you want to go the loop way, I would suggest to create an array of FirstName and LastName and use union to loop through it. Now you could use filter array, select and compose to create your record. After the loop you have the array you wanted by using the output of the compose action.

     

    I would go the xpath way.

  • Verified answer
    Chriddle Profile Picture
    Chriddle 8,436 Super User 2025 Season 2 on at

    From CSV to Expected Structure in only 2 Select actions.

    However, this CSV parsing only works in simple cases.

     

    Chriddle_0-1709041845927.png

    DataCsv (Compose)

    FirstName,LastName,Class,Teacher
John,Doe,Math,Carlos
John,Doe,English,Anna
John,Doe,Art,Kim
Sarah,Miller,Math,Kelly
Sarah,Miller,Art,Matt

     

    Data (Select)

    From

    skip(
	split(
		outputs('DataCsv'),
		decodeUriComponent('%0A')
	),
	1
)

    Map FirstName

    split(item(), ',')[0]

    Map LastName

    split(item(), ',')[1]

    Map ScheduleString

    concat(
	'{"Class":"', split(item(), ',')[2], '","Teacher":"', split(item(), ',')[3], '"}'
)

     

    Select

    From

    union(
	chunk(
		xpath(
			xml(json(concat('{"Root":{"Item":', body('Data'),'}}'))),
			'//FirstName/text() | //LastName/text()'
		),
		2
	),
	json('[]')
)

    Map FirstName

    first(item())

    Map LastName

    last(item())

    Map Schedule

    json(
	concat(
		'[',
		join(
			xpath(
				xml(json(concat('{"Root":{"Item":', body('Data'),'}}'))),
				concat('//Item[FirstName="', first(item()), '" and LastName="', last(item()), '"]/ScheduleString/text()')
			),
			','
		),
		']'
	)
)

     

    Result:

    [
 {
 "Firstname": "John",
 "LastName": "Doe",
 "Schedule": [
 {
 "Class": "Math",
 "Teacher": "Carlos"
 },
 {
 "Class": "English",
 "Teacher": "Anna"
 },
 {
 "Class": "Art",
 "Teacher": "Kim"
 }
 ]
 },
 {
 "Firstname": "Sarah",
 "LastName": "Miller",
 "Schedule": [
 {
 "Class": "Math",
 "Teacher": "Kelly"
 },
 {
 "Class": "Art",
 "Teacher": "Matt"
 }
 ]
 }
]

     

  • chlxco Profile Picture
    chlxco 8 on at

    Thank you so much Chriddle! I replicated the scenario and worked as a charm. Though I will be adding more fields to "group by", I think I understand now the concept about mapping and using xpath as an option. As a reference, I was trying to solved based on @devtollon  reply on this post but my flow was getting oddly big 😅

    Thanks again for the detailed answer, and thank you all for your support on this.

     

    Cheers,

  • chlxco Profile Picture
    chlxco 8 on at

    Hey there,

     

    I have an extra question for you that I have been unable to overcome once I got my object created as you instructed. The array within the object (Schedule) looks as an entire string and when trying to parse it to create the final JSON, it doesnt create the array.. Here an example of what I get after I run the Select action in your answer:

     

    {
    "body": [
    {
    "FirstName": "a",
    "LastName": "b",
    "Schedule": "[{\"Class\":\"c\",\"Teacher\":\"d\"},{\"Class\":\"e\",\"Teacher\":\"f\"}]"
    },...... next item in the array

    This is what I get after using Parse to JSON action:

    {
    "body": [
    {
    "FirstName": "a",
    "LastName": "b",
    "Schedule": "[{\"Class\":\"c\",\"Teacher\":\"d\"},{\"Class\":\"e\",\"Teacher\":\"f\"}]"
    },{....next item

     

    I have tried changing the schema from Schedule attribute from String to array, object.. without success.

     

    Here what Im expecting to get from the parse to JSON action:

    {
    "body": [
    {
    "FirstName": "a",
    "LastName": "b",
    "Schedule": [
                {"Class":"c","Teacher":"d"},
                {"Class":"e","Teacher":"f"}
                ]
    },....... and so on.

    Thanks again,

     

  • Chriddle Profile Picture
    Chriddle 8,436 Super User 2025 Season 2 on at

    Converting the ScheduleStrings (in "Data") to Array is done in my “Map Schedule” above using json(...) function.

    I don't understand what you mean by "I tried to change the schema..."

  • tbar44 Profile Picture
    tbar44 5 on at

    Thanks for this, works as expected and is nice and quick.

     

    Is it possible to use similar to add another layer?

     

     

    So if the table was something like;

     

    FirstNameLastNameClassTeacherAssignment1Assignment2
    JohnDoeMathCarlosAlgebraCalculus
    JohnDoeEnglishAnnaReportEssay
    JohnDoeArtKimPaintingSculpture
    SarahMillerMathCarlosAlgebraCalculus
    SarahMillerEnglishAnnaReportEssay
    SarahMillerArtKimPaintingSculpture

     

    The output would look something like;

     

    [{
 "Firstname": "John",
 "LastName": "Doe",
 "Schedule": [
 {
 "Class": "Math",
 "Teacher": "Carlos",
	"Assignments": [
		{
 	"Assignment1": "Algebra",
 	"Assignment2": "Calculus"
 	}
	]
 },
 {
 "Class": "English,
 "Teacher": "Anna",
	"Assignments": [
		{
 	"Assignment1": "Report",
 	"Assignment2": "Essay"
 	}
	]
 },
 {
 "Class": "Art",
 "Teacher": "Kim",
	"Assignments": [
		{
 	"Assignment1": "Painting",
 	"Assignment2": "Sculpture"
 	}
	]
 },
 ]
 },
{
 "Firstname": "Sarah",
 "LastName": "Miller",
 "Schedule": [
 {
 "Class": "Math",
 "Teacher": "Carlos",
	"Assignments": [
		{
 	"Assignment1": "Algebra",
 	"Assignment2": "Calculus"
 	}
	]
 },
 {
 "Class": "English,
 "Teacher": "Anna",
	"Assignments": [
		{
 	"Assignment1": "Report",
 	"Assignment2": "Essay"
 	}
	]
 },
 {
 "Class": "Art",
 "Teacher": "Kim",
	"Assignments": [
		{
 	"Assignment1": "Painting",
 	"Assignment2": "Sculpture"
 	}
	]
 },
 ]
 }]

     

    Not sure if this is outside the limits of what you have done?

    I did try to tweak the code myself but suspect I'm doing something dim!

     

    Thanks!

     

  • Chriddle Profile Picture
    Chriddle 8,436 Super User 2025 Season 2 on at

    Why would you put exactly one object into an array?

    Chriddle_0-1716994804113.png

    I discarded that and went for a structure like this:

    "Assignments": {

        "Assignment1": "Algebra",

        "Assignment2": "Calculus"

    }

     

    To do this, all you need to do is add these new columns to the Data action above.

    Map ScheduleString

    concat(
	'{"Class":"', split(item(), ',')[2], '","Teacher":"', split(item(), ',')[3], '", "Assignments":{"Assignment1": "', split(item(), ',')[4], '", "Assignment2": "', split(item(), ',')[5], '"}}'
)

     

  • tbar44 Profile Picture
    tbar44 5 on at

    Thanks, sorry my bad, my example could have been better.

    The format is set out by the data being ingested by an API.

     

    Was trying to mimic the data format and realise I did a poor job.

     

    Better example would be this.

    FirstNameLastNameClassTeacherAssignmentDue Date
    JohnDoeMathCarlosAlgebra2024-06-01
    JohnDoeMathCarlosCalculus2024-07-01
    JohnDoeEnglishAnnaReport2024-08-01
    JohnDoeArtKimPainting2024-09-01
    SarahMillerMathCarlosAlgebra2024-10-01
    SarahMillerEnglishAnnaReport2024-11-01
    SarahMillerEnglishAnnaEssay2024-12-01
    SarahMillerArtKimPainting2025-01-01

     

    So would look something like this.

     

    [{
 "Firstname": "John",
 "LastName": "Doe",
 "Schedule": [
 {
 "Class": "Math",
 "Teacher": "Carlos",
	"Assignments": [
		{
 	"Assignment": "Algebra",
 	"Due Date": "2024-06-01"
 	},
	{
 	"Assignment": "Calculus",
 	"Due Date": "2024-07-01"
 	}
	]
 },
 {
 "Class": "English,
 "Teacher": "Anna",
	"Assignments": [
		{
 	"Assignment": "Report",
 	"Due Date": "2024-08-01"
 	}
	]
 },
 {
 "Class": "Art",
 "Teacher": "Kim",
	"Assignments": [
		{
 	"Assignment": "Painting",
 	"Due Date": "2024-09-01"
 	}
	]
 },
 ]
 },
{
 "Firstname": "Sarah",
 "LastName": "Miller",
 "Schedule": [
 {
 "Class": "Math",
 "Teacher": "Carlos",
	"Assignments": [
		{
 	"Assignment": "Algebra",
 	"Due Date": "2024-10-01"
 	}
	]
 },
 {
 "Class": "English,
 "Teacher": "Anna",
	"Assignments": [
		{
 	"Assignment": "Report",
 	"Due Date": "2024-11-01"
 	},
	{
 	"Assignment": "Essay",
 	"Due Date": "2024-12-01"
 	}
	]
 },
 {
 "Class": "Art",
 "Teacher": "Kim",
	"Assignments": [
		{
 	"Assignment": "Painting",
 	"Due Date": "2025-01-01"
 	}
	]
 },
 ]
 }]

     

    Apologies for the confusion!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 522 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 364 Moderator

#3
abm abm Profile Picture

abm abm 243 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Flows are not visible for users who sign in with their personal accounts
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Question for everyone : How are you using Create Text GPT in AI Builder?
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans