Solved: JSON objects into single Create Item

JSON objects into single Create Item

I have a flow where I want to process some JSON data returned from an API and create an item in a SharePoint list for each response. (The SharePoint List needs to be in a flat format, i.e. a column for each of the 'V' numbers, V48, V2, V66 etc.)

I've done the bulk of the processing fine but am coming stuck at the part where I create the item.

For instance, I've processed the data and ended up with a Select action with the output:

[
 {
 "V48": "1"
 },
 {
 "V2": "1"
 },
 {
 "V65": "457b67fc-edf8-41e2-8a44-1939da32c6ee"
 },
 {
 "V3": "17/07/2023"
 },
 {
 "V4": "13:10:53"
 },
 {
 "V5": "17/07/2023"
 },
 {
 "V6": "13:14:54"
 },
 {
 "V7": "4.02"
 },
 {
 "V55": "201127"
 }
]

If I Parse this response, and then put each value into a Create Item action, it puts the action into an Apply To Each loop and then creates multiple rows in the list with null values, i.e.

V48	V2	V65	V3
1
	1
		457b67fc-edf8-41e2-8a44-1939da32c6ee
			17/07/2023

If I don't do the Parsing, and use expressions in the create item, such as:

first(outputs('Select'))['V65']

I get an error message: Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'The template language function 'first' expects its parameter be an array or a string. The provided value is of type 'Object'.

I'm missing something simple, but I cannot get my head around what the answer is.

Right, of course as soon as I posted this I stumbled onto the (an?) answer.

This blog helped massively. You have to turn the objects into a string, so you can replace the curly brackets thus making it a single object, then transform it back to JSON. His answer of having a Compose action with the expression:

first(json(replace(string(outputs('Compose')),'},{',',')))

(assuming 'Compose' is the output of my select action here)

kept failing for me, so I had to go one step backwards and just use:

json(replace(string(outputs('Compose')),'},{',','))

I then Parsed that and used each value to populate the create item columns. At this point it did pop the create items action into an Apply To Each loop, but that was fine in this scenario.

Quick Links