I was trying this snippet to split my json array.
activity =
//to extract json object required, only "activity" field is to be parsed and not "nameOfWebsite"
EXTRACT activities : string
FROM @input
USING Extract.Json(rowPath: "[]");
activities_arr =
SELECT
//splitting into array based on delimiter
new ARRAY<string>(activities.Split(',')) AS activities
FROM activity
;
activities_output =
SELECT activities
FROM activities_arr AS ac
CROSS APPLY EXPLODE(ac.activities) AS activities //to split above array into rows
;
Input is like this
[
{
"nameOfWebsite": "StackOverflow", // this object is not required
"activities": [
"Python",
"U-SQL",
"JavaScript"
]
}
]
So, currently I am getting output as: 5 columns with one column as some random string not in input followed by 3 blank columns and then the 5th column contains Python, U-SQL, JavaScript in separate rows. Questions:
- Is there any way to avoid the 4 other columns as I only require data 4th column ie. only the name of activities?
- Why are there blank spaces in my current output when my delimiter is defined as ','? Current output ("blank" denotes blank space and not string blank)
AB#### "blank" "blank" "blank" Python
AB#### "blank" "blank" "blank" U-SQL
AB#### "blank" "blank" "blank" JavaScript
Output expected
Python
U-SQL
JavaScript