Creating a table in ADB SQL for multiple JSON files and selecting all the rows from all the files

[adb_newbie]

Hi

I have multiple json files stored in my ADLS2 and I want to create a table in which will directly read all the data from ADLS without mounting the files. When I create the table, i cannot select all the data How can i achieve this.

ADLS Path : /dwh/test/visit/visit_dt=2024-07-01

File1 : [{ "results": [ { "visit": { "id": "123456"} }, {"visit": { "id":"7890"}} ] }]

File2 : [{ "results": [ { "visit": { "id": "234567"} }, {"visit": { "id":"8901"}} ] }]

File3 : [{ "results": [ { "visit": { "id": "345678"} }, {"visit": { "id":"9012"}} ] }]

Create table :

create table my_visits 

(

results ARRAY<struct<visit: STRUCT<

id: STRING

>>>, visit_dt STRING

)

USING JSON

partition by (visit_dt)

options (

path='wdbss://dwh@devdatalake.dfs.core.windows.net/test/visit",

multiline = TRUE

);

When I select all the data from the table, I get only selected records

Query : select results.visit.id from my_visits;

Output: 

["123456","7890"]

["234567","8901"]

["345678","9001"]

Query: select results[0].visit.id from my_visits;

Output:

123456

234567

345678

How can i get all the visit.id in a single rows from all the files.

Any help is appreciated.

Regards