Background: I have a CSV file with a column that has a list of tags for a given row. The tag list is not in any specific order and varies for each cell in the tags column. I am looking for the value for a row which matches the string "Owner". When pulling in the CSV file, the entire cell is 1 string per cell. An example cell in this column looks like following:
"Organization": "Microsoft", "Owner": "Eric Holmes", "DateCreated": "07/09/2021"
Goal: I would like to find a way in Azure Data Flows or Azure Data Factory to make a new column with a value for a specific key in a list.
Example:
Current Column
Tags
"Department": "Business", "Owner": "Karen Singh", "DateCreated": "09/20/2019"
"Owner": "Henry Francis", "AppName": "physics-engine", "Department": "GeospatialServices"
"Department": "Fashion", "DateCreated": "01/10/2015", "Owner": "Xiuxiang Long"
Desired Column
Owner
"Karen Singh"
"Henry Francis"
"Xiuxiang Long"
Work So Far: I have taken each string in the tags column split it into an array by breaking it apart and the commas (,). Then I have split each string at each index by the colon (:). This makes the values look like:
Tags
[["Department", "Business"], ["Owner", "Karen Singh"], ["DateCreated", "09/20/2019"]]
[["Owner", "Henry Francis"], ["AppName", "physics-engine"], ["Department", "GeospatialServices"]]
[[Department", "Fashion"], ["DateCreated", "01/10/2015"], ["Owner", "Xiuxiang Long"]]
To split the strings, I've used this open expression
mapIndex(split(replace(Tags, '"', ''), ','), split(#item, ':'))
Problems I am new to Open Expressions and Azure Data Factory and Data Flows. Does anyone know how I would:
- Search for the desired tag like "Owner"
- And return the value associated to it
Sorry I know this question sounds very simple but using only open expression functions makes this more convoluted than necessary. Additionally, if there is a better way to go about this problem I'd appreciate any input! I've been banging my head against the wall and any leads help. Thank you!
