0

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:

  1. Is there any way to avoid the 4 other columns as I only require data 4th column ie. only the name of activities?
  2. 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
  • U-SQL is deprecated, see [here](https://stackoverflow.com/a/66700263/1527504). The best advice I could give you is either do it in SQL which has STRING_SPLIT or Synapse Notebook or Azure Databricks Notebook. – wBob Aug 04 '22 at 21:39
  • I have to implement it using U-SQL only. Thanks anyways. – LastCodebender Aug 04 '22 at 22:02

0 Answers0