Split array into column

Question

Hello, I have an array that is like below . Being the same element name, they are being combined when i try to flatten or using a derived column. To make matter more complex, the below should end up as 8 columns so a Flatten is ruled out. I was thinking of using split or a combination of substring and instr, but to no avail. Any recommendations on how to achieve this in the dataflow.

ns0:REF_3": [
					{
						"REF01": "A0F",
						"REF02": "1001"
					},
					{
						"REF01": "1L",
						"REF02": "DB"
					},
					{
						"REF01": "21",
						"REF02": "900D"
					},
					{
						"REF01": "90",
						"REF02": "3860496"
					}
				]

Answer 1

Hello @sam nick , Thanks for the question and using MS Q&A platform. I am not sure as to how you are planning to use this , but I think I was able to do with with split ,replace and join function . For simplicity I started with the below string.

[{
	"REF01": "A0F",
	"REF02": "1001"
}, {
	"REF01": "1L",
	"REF02": "DB"
}, {
	"REF01": "21",
	"REF02": "900D"
}, {
	"REF01": "90",
	"REF02": "3860496"
}]

Steps

1. Transformed this into a simpler string by using the dynamic expression

   @replace(replace(replace(replace(replace(variables('jsonstring'),'[{','' ),'}, {',','),'}]',''),'"REF01":',''),'"REF02":','')

1. Used the string from step 1 and used the split function as added the text in an array

   @split(variables('jsonstring1'),',' )

1. Used the join function on the array .

@join(variables('array1'),',' )

This is output you have.

Let me know if this helps.

Thanks Himanshu Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.