How to convert Array(Struct(Struct)) to Array(Struct) in Azure Databricks SQL

Question

I have tried using the Azure SQL explode() function and it doesn't work for my use case. And when selecting from_json just expects the same structure I have defined in the table. I cannot change the source table structure unfortunately as per design. But, when pulling the data out, I need it changed/flattened.

select  from_json(Payload:Employees[*],'array<struct<`Person`:struct<`Name`:string,`Address`:struct<`Line1`:string,`Line2`:string>,`Service`:string>>', map('multiline', 'true')) from delta.`/mnt/empsource`

An example of my data structure in the Azure data bricks table is

array
0:
--Person: struct
----Name: value0
----Address: struct
------Line1: value0
------Line2: value0
--Service: value
1:
--Person: struct
----Name: value1
----Address: struct
------Line1: value1
------Line2: value1
--Service: value

I need this to be output as

array
0:
--Name: value0
--Line1: value0
--Line2: value0
--Service: value0
1:
--Name: value1
--Line1: value1
--Line2: value1
--Service: value1

Answer 1

You can do it with transform function in Databricks SQL.

Here, this is the sample JSON string which I converted into dataframe and then into an SQL view.

{
"arr":
[
{
"Person": 
{
"Name" : "Rakesh",
"Address":
{
"Line1":"H.No1",
"Line2":"Streetname"
}
},
"Service":"value"
},
{
"Person": 
{
"Name":"Raju",
"Address":
{
"Line1":"H.No2",
"Line2":"Streetname"
}
},
"Service":"value"
}
]
}

Use the transform function like below:

select transform(arr,a -> struct(a.Person.Name,a.Person.Address.Line1 as Line1,a.Person.Address.Line2 as Line2,a.Service)) as arr from mydf

Here mydf is the view name of the JSON.

Please check these outputs for your reference:

Data structure before:

Output structure:

Reference:

https://docs.databricks.com/sql/language-manual/functions/transform.html