extract json column using spark.sql in azure synapse notebook

Question

I have a parquet file as source and I loaded that parquet file using PySpark notebook as shown below:

df_Employee = spark.read.parquet(<filename>)
df_Employee .createOrReplaceTempView("employee_data")

here's how the table looks like


Employee Table:

-Name
-Salary
-Company
-Address (datatype=string)
 --street.name
 --street.number
 --postalcode
-JoiningDate

I have the following code but I'm stuck on how to extract street.name & street.number from the above SQL table, here's what I have

df=spark.sql(f'''
select Name, Salary, Company, json_extract(Address,'$."street.name"') as StreetName
from employee_data
''')

but json_extract(Address,'$."street.name"') as StreetName throws an error. How to extract this nested json field?

select Name, Salary, Company, Address.street.name as StreetName , Address.street.number as StreetNumber ? — anky
– anky, Commented Jan 19, 2023 at 20:22
we dont know your data. please create a minimal reproducible example — anky
– anky, Commented Jan 19, 2023 at 20:38

Vamsi Bitra · Accepted Answer · 2023-01-20 11:27:24Z

1

I reproduce the same in my environment created sample data frame as per Employee Table :

dat1= [("vamsi", 20000, "MID", '{"street.name": "App socity", "street.number": "912", "postalcode": "523112"}', "2023-01-20"),
        ("rakesh",30000, "MID", '{"street.name": "Mind space", "street.number": "456", "postalcode": "600062"}', "2023-01-19")]
col = ["Name", "Salary", "Company", "Address", "JoiningDate"]

df1 = spark.createDataFrame(dat1, col)
df1.createOrReplaceTempView("sample_table")

enter image description here

You can achieve the same requirement using the following code.

df1 = spark.sql(f'''select Name, Salary, Company, json_tuple(Address, 'street.name', 'street.number') as (StreetName, StreetNumber)
from sample_table''')
df1.show()

enter image description here

answered Jan 20, 2023 at 11:27

Vamsi Bitra

2,7642 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

extract json column using spark.sql in azure synapse notebook

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related