How to create dataset for flattend json in sql server

1

Hi ,

I have flattened column in sql database like in screen-shots ,how can i create dataset so that each field in Schema is seprate coloumn in dremio.do we have any api or refrance for that

eg: i have following json schema in Sql server as colomn ,how can i flattend in dremio so that each field is seprate coloumn here

{“parent”:“47474565-b688-460b-a33d-3495eda82712”,“projectname”:“MR GAT”,“entitytimezone”:“2020-06-25”,“modifieddatetime”:“2020-06-25”,“entityid”:“47474565-b688-460b-a33d-3495eda82712”,“createddatetime”:“2020-06-25”,“uuid”:“47474565-b688-460b-a33d-3495eda82712”,“datestarted”:“2020-06-23”,“projectuom”:“Metric”}

image
image1232×285 11 KB

2

@Vikash_Singh

if you have a list (array) then you can unnest (FLATTEN) and then extract a column, see example below

SELECT fragmentProfile, nested_0.fragmentProfile.majorFragmentId AS majorFragmentId
FROM (
SELECT flatten(fragmentProfile) AS fragmentProfile
FROM “@dremio”.“profile-prod” AS “profile-prod”
) nested_0