Flatten an array within an object, without variables

[AndrewU]

Hi,

I have an array of objects, with one of the object properties, assessments, being an array of objects. I want to flatten assessments into a lightly formatted string and retaining all other object properties.

I'm sure I could achieve this with the aid of a couple of variables, however this process sits within a higher level Apply. To take advantage of concurrent running I'd like to avoid the use of variables altogether if possible.

For added awkwardness, the ordering of the properties of the objects in assessments are not in a predictable order.

Any thoughts? 

Thanks

Source JSON:

[
    {
        "id": 100,
        "asset": "Thimgamajig",
        "status": 2,
        "assessments": [
            {
                "assessor": "Alice",
                "decision": "Satisfactory.",
                "feedback": ""
            },
            {
                "decision": "Not satisfactory.",
                "feedback": "Documentation out of date.",
                "assessor": "Bob"
            },
            {
                "feedback": "",
                "assessor": "Charlie",
                "decision": "Satisfactory."
                
            }
        ]
    },
    {
        "id": 101,
        "asset": "Whatchamacalit",
        "status": 1,
        "assessments": [
            {
                "assessor": "Alice",
                "decision": "Satisfactory.",
                "feedback": ""
            },
            {
                "decision": "Satisfactory.",
                "feedback": "Excellent.",
                "assessor": "Dan"
            }
        ]
    }
]

Desired output:

[
    {
        "id": 100,
        "asset": "Thimgamajig",
        "status": 2,
        "assessments": "Alice - Satisfactory. \n Bob - Not satisfactory. Documentation out of date. \n Charlie - Satisfactory."
    },
    {
        "id": 101,
        "asset": "Whatchamacalit",
        "status": 1,
        "assessments": "Alice - Satisfactory. \n Dan - Satisfactory. Excellent."
    }
]

[v-bofeng-msft]

Hi @AndrewU ,

I've made a test for your reference:

My flow

item()['id']

item()['asset']

item()['status']

replace(replace(replace(replace(replace(replace(join(item()['assessments'],'\n'),'"',''),'{',''),'}',''),'assessor:',''),'decision:',''),'feedback:','')

Result:

Best Regards,

Bof

[AndrewU]

Thanks @v-bofeng-msft . This would work great if I could rely on the properties being a particular order e.g. if it was always assessor, decision, feedback. Unfortunately they can appear in any ordering 😒

I'm now looking at whether the xpath function can offer a solution. I just can't see how to construct a xpath query that will join multiple strings (have tried concat and string-join)

[Chriddle · Accepted solution]

It's easy if you have a maximum length of assessments per asset:

concat(
	if(
		greater(
			length(item()['assessments']),
			0
		),
		concat(item()['assessments'][0]['assessor'], ' - ', item()['assessments'][0]['decision'], ' ', item()['assessments'][0]['feedback']),
		''
	),
	if(
		greater(
			length(item()['assessments']),
			1
		),
		concat('\n', item()['assessments'][1]['assessor'], ' - ', item()['assessments'][1]['decision'], ' ', item()['assessments'][1]['feedback']),
		''
	),
	if(
		greater(
			length(item()['assessments']),
			2
		),
		concat('\n', item()['assessments'][2]['assessor'], ' - ', item()['assessments'][2]['decision'], ' ', item()['assessments'][2]['feedback']),
		''
	)
)

Result:

[
  {
    "assessments": "Alice - Satisfactory. \\nBob - Not satisfactory. Documentation out of date.\\nCharlie - Satisfactory. "
  },
  {
    "assessments": "Alice - Satisfactory. \\nDan - Satisfactory. Excellent."
  }
]

[AndrewU · Accepted solution]

Thanks @Chriddle , this should do the trick. In theory there may be upto 20 assessments (eww), but in reality there's rarely more than 4. Think I'll take this to 5, with a "... plus ..." if there are any more and see if anyone notices!