filter data in Spark SQL in Databrick

user_15175378 0
Viewed 200

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

databricks-sql databricks apache-spark-sql
1 Answers

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

  1. Use common table expressions to explode first & then filter:
with exploded as (
  SELECT inline(environment.details) FROM TableA
) 
SELECT * from exploded where name = ....
  1. 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
Alex_Ott_186270
Related