Hi,
Using the below cosmos DB query it is possible to achieve the expected output, but how can I do the same with spark SQL in Databricks.
COSMOSDB QUERY : select c.ReportId,c.ReportName,i.price,p as provider from c join i in in_network join p in i.provider
Source JSON
{
"ReportId":"F0001",
"ReportName":"ALYX_HLT",
"in_network":[
{"provider":[1,2,3,4],"price":10},
{"provider":[1004],"price":100.2},
{"provider":[39,52],"price":3}
]
}
Expected Output
[
{ "ReportId":"F0001","ReportName":"ALYX_HLT","provider":100,"price":10},
{ "ReportId":"F0001","ReportName":"ALYX_HLT","provider":200,"price":10},
{ "ReportId":"F0001","ReportName":"ALYX_HLT","provider":300,"price":1.3},
{ "ReportId":"F0001","ReportName":"ALYX_HLT","provider":400,"price":23.1},
{ "ReportId":"F0001","ReportName":"ALYX_HLT","provider":500,"price":23.1}
]
https://docs.microsoft.com/en-us/answers/questions/821351/trying-to-flattren-my-json-using-cosmosdb-...