Split array into column

sam nick 286 Reputation points
2023-04-05T02:52:32.21+00:00

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"
					}
				]
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,711 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2023-04-07T19:29:05.1333333+00:00

    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.
    User's image

    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.