Resolved: filter data in Spark SQL in Databrick

Question:

When I run below command in Databrick , I get output where I have three columns called name, id and age but when I try to filter on name by running below I get below error as Name column do not exist. What wrong am i doing?

%sql
SELECT inline(environment.details) FROM TableA

This gives me a table with 3 column correctly.
Now I do filter oon Name like this

%sql
    SELECT inline(environment.details) FROM TableA where `Name` == "XYZ"

and I get error as Name table do not exist.What wis wrong here. Also if someone can tell me how can I export the resultant output.
Thanks

Answer:

Filtering happens before your expand your array of structs. You have two choices here:

Use common table expressions to explode first & then filter:

with exploded as (
  SELECT inline(environment.details) FROM TableA
) 
SELECT * from exploded where name = ....

Use the filter function to filter out data inside the array with something like that (not tested), but it may require doing the filtering two times:

SELECT inline(filter(environment.details, x -> x.Name = 'XYZ')) 
FROM TableA 
WHERE array_size(filter(environment.details, x -> x.Name = 'XYZ')) > 0

If you have better answer, please add a comment about this, thank you!

Resolved: filter data in Spark SQL in Databrick

Resolved: Unable to run Docker .NET 6 console image

Resolved: Input Focus between two React Components

Resolved: linq2db throws exception when filtering by nested collection

Leave A Reply