tsql to create extract key and value from JSON data in a serverless sql pool

Question

How to create external table using Serverless SQL pool using tsql in Azure Synapse Analytics for this scenario:

I have a 'employeeInfo' column in a table called as 'companyDetail'. This is the query to create 'companyDetail' table:

CREATE EXTERNAL TABLE companyDetail
(
  companyName varchar(100),
  employeeInfo varchar(2048) 
) 
WITH
(
    LOCATION = '/all_parquet_files/*.parquet',
    DATA_SOURCE = parquet_datasource,
    FILE_FORMAT = parquet
)
 

companyDetail table data:

companyName| employeeInfo
----------------------------
ABC        | {name: Ramesh, age:32 years}
ABC        | {name: Mohan, experience:2 years}
DEF        | {name: Dinesh, age:39, experience:5 years}
HIJ        |
DEF        | {name: Mohit}

I have to create an external table 'employee' from this data which should have this result:

companyName| employeeKey | employeeValue
------------------------------------------
ABC        | name        | Ramesh
ABC        | age         | 32 years
ABC        | name        | Mohan
ABC        | experience  | 2 years
ABC        | name        | Dinesh
ABC        | age         | 39
ABC        | experience  | 2 years
HIJ        |             |
DEF        | name        | Mohit

Answer 1

One approach would be to import the data from the external table into a temporary table or a table variable, and then use the OPENJSON function on the imported data to flip the data into columns. e.g:

SELECT companyName, employeeInfo
INTO #tempTable
FROM companyDetail
-- WHERE ...

SELECT companyName, [Key] as employeeKey, [Value] as employeeValue
FROM #tempTable
CROSS APPLY OPENJSON(employeeInfo)

see: OPENJSON