Hi Folks,
I have created this query in snowflake to convert JSON format data into table structure:
select *, doc:"_class"::STRING AS Class,
doc:"_id"::STRING AS Id,
doc:"mvrId"::STRING AS mvr_id,
doc:"orderNumber"::STRING AS Order_Number,
doc:driverLicenseRecord:record:dlRecord:criteria:firstName::STRING AS criteria_First_Name,
doc:driverLicenseRecord:record:dlRecord:criteria:host::STRING AS criteria_Host,
doc:driverLicenseRecord:record:dlRecord:criteria:lastName::STRING AS criteria_Last_Name,
doc:driverLicenseRecord:record:dlRecord:criteria:licenseNumber::STRING AS criteria_license_Number,
doc:driverLicenseRecord:record:dlRecord:criteria:accountId::STRING AS criteria_Account_Id,
doc:driverLicenseRecord:record:dlRecord:criteria:birthDate:full::DATE AS criteria_Birth_Date,
doc:driverLicenseRecord:record:dlRecord:criteria:orderDate:full::DATE AS criteria_Order_Date,
doc:driverLicenseRecord:record:dlRecord:criteria:productID::STRING AS criteria_Product_ID,
doc:driverLicenseRecord:record:dlRecord:criteria:reference::STRING AS criteria_Reference,
doc:driverLicenseRecord:record:dlRecord:criteria:routing::STRING AS criteria_Routing,
doc:driverLicenseRecord:record:dlRecord:criteria:state:full::STRING AS criteria_State_Full,
doc:driverLicenseRecord:record:dlRecord:criteria:subtype::STRING AS criteria_Sub_type,
doc:driverLicenseRecord:record:dlRecord:criteria:subtypeFull::STRING AS criteria_Subtype_Full,
doc:driverLicenseRecord:record:dlRecord:criteria:trackingNumber::STRING AS criteria_Tracking_Number,
doc:driverLicenseRecord:record:dlRecord:currentLicense:licenseInformation:classCode::STRING AS currentLicense_Class_Code,
doc:driverLicenseRecord:record:dlRecord:currentLicense:licenseInformation:classDescription::STRING AS currentLicense_Class_Description,
doc:driverLicenseRecord:record:dlRecord:currentLicense:licenseInformation:type::STRING AS currentLicense_Type_List,
doc:driverLicenseRecord:record:dlRecord:currentLicense:number::STRING AS currentLicense_Number,
doc:driverLicenseRecord:record:dlRecord:driver:age::STRING AS driver_Age,
doc:driverLicenseRecord:record:dlRecord:driver:birthDate:full::DATE AS driver_Birth_Date,
doc:driverLicenseRecord:record:dlRecord:driver:firstName::STRING AS driver_First_Name,
doc:driverLicenseRecord:record:dlRecord:driver:gender::STRING AS driver_Gender,
doc:driverLicenseRecord:record:dlRecord:driver:height::NUMBER AS driver_Height,
doc:driverLicenseRecord:record:dlRecord:driver:lastName::STRING AS driver_Last_Name,
doc:driverLicenseRecord:record:dlRecord:driver:eyeColor::STRING AS driver_Eye_Color,
doc:driverLicenseRecord:record:dlRecord:driver:hairColor::STRING AS driver_Hair_Color,
doc:driverLicenseRecord:record:dlRecord:driver:weight::NUMBER AS driver_Weight,
doc:driverLicenseRecord:record:dlRecord:currentLicense:licenseInformation:expirationDate:full::STRING AS currentLicense_Expiration_Date,
doc:driverLicenseRecord:record:dlRecord:currentLicense:licenseInformation:issueDate:full::STRING AS currentLicense_Issue_Date,
doc:driverLicenseRecord:record:dlRecord:result:control::STRING AS result_Control_List,
doc:driverLicenseRecord:record:dlRecord:result:returnedDate:full::DATE AS result_Returned_Date,
doc:driverLicenseRecord:record:dlRecord:result:valid::STRING AS result_Valid_List,
Address_List.value:"City"::STRING AS AddressList_City_List,
Address_List.value:"County"::STRING AS AddressList_County_List,
Address_List.value:"State":Full::STRING AS AddressList_StateFull_List,
Address_List.value:"Street"::STRING AS AddressList_Street_List,
Address_List.value:"Zip"::STRING AS AddressList_Zip_List,
Message_List.value:"line"::STRING AS Message_Line_List,
Event_List.value:"Common":Date:Full::DATE AS EventList_Common_Date,
Event_List.value:"Common":PostedDate:Full::STRING AS EventList_Posted_Date,
Event_List.value:"Common":IsCommercial::STRING AS EventList_IsCommercial_List,
Event_List.value:"Common":IsHazMat::STRING AS EventList_IsHazMat_List,
Event_List.value:"Common":State:Full::STRING AS EventList_StateFull_List,
Event_List.value:"Common":Type::STRING AS EventList_Type_List,
Event_List.value:"Common":Subtype::STRING AS EventList_SubType_List,
Event_List.value:"Violation":ConvictionDate:Full::DATE AS Violation_Date,
Event_List.value:"Action":ClearDate:Full::Date AS action_Clear_Date,
Event_List.value:"Action":MailDate:Full::STRING AS action_Mail_Date,
Description_List.value:"Acd"::STRING AS Acd_List,
Description_List.value:"AdrLargeDescription"::STRING AS AdrLarge_Description_List,
Description_List.value:"AdrSmallDescription"::STRING AS AdrSmall_Description_List,
Description_List.value:"Avd1"::STRING AS Avd1_List,
Description_List.value:"Avd2"::STRING AS Avd2_List,
Description_List.value:"Avd3"::STRING AS Avd3_List,
Description_List.value:"StateAssignedPoints"::STRING AS State_Assigned_Points_List,
Description_List.value:"StateCode"::STRING AS State_Code_List,
Description_List.value:"StateDescription"::STRING AS State_Description_List,
Description_List.value:"TableKey"::STRING AS Table_Key_List
from (
select parse_json("_doc") as doc from "AIGI_EDR_REPLICATEDDATA"."LOSS_SENSITIVE_DEV_DATA"."mvr_db_driver_license_record"),
LATERAL FLATTEN(input => doc:driverLicenseRecord:record:dlRecord:driver:addressList) AS Address_List,
LATERAL FLATTEN(input => doc:driverLicenseRecord:record:dlRecord:messageList) AS Message_List,
LATERAL FLATTEN(input => doc:driverLicenseRecord:record:dlRecord:eventList) AS Event_List,
LATERAL FLATTEN(input => Event_List.value:"DescriptionList") AS Description_List;
when use Snowflake as my data source and put this query into SQL Native query and used Direct query mode.
I am getting this error:
Expression.Error: The field 'SEQ' already exists in the record.
Could you please help me to resolve this issue?
Regards,
Ranjeet Jha
