Flatten json schema update showing error in dremio

1

I have nested json in MsSql Db ,i have schema DB column added and updated ,when i am trying to query dremio on the nested json with new filed which not exist in nested json ,its whould atlest give null ,same query working in ssms tools

here is the query which i have used in msql

select JSON_QUERY(payload,’$.da’) as da, * from dailyjournal_review

image
image1130×496 37.3 KB

but failed in dremio as refrance not there

SELECT ParentEntityId,‘null’ AS new_field, Parent, UUID, Payload, newadditionalinspectionitem,additionalinspectionitem, treeStatus, additionalinspectionitemvisualinspection, CAST(failadditionalinspectionitemvisualinspectioncorrected as VARCHAR(2048)) AS failadditionalinspectionitemvisualinspectioncorrected, additionalinspectioncomments, Type, EntityTimeZone, CreatedBy, ModifiedBy, ModifiedDateTime, CreatedDateTime, SyncDateTime, CreatedByUserId, ModifiedByUserId
FROM (
SELECT “ParentEntityId”, “Parent”, “UUID”, “Payload”,“nested_0”.“Payload”[’’] AS “newadditionalinspectionitem”, “nested_0”.“Payload”[‘additionalinspectionitem’] AS “additionalinspectionitem”, “nested_0”.“Payload”[‘treeStatus’] AS “treeStatus”, “nested_0”.“Payload”[‘additionalinspectionitemvisualinspection’] AS “additionalinspectionitemvisualinspection”, “nested_0”.“Payload”[‘failadditionalinspectionitemvisualinspectioncorrected’] AS “failadditionalinspectionitemvisualinspectioncorrected”, “nested_0”.“Payload”[‘additionalinspectioncomments’] AS “additionalinspectioncomments”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM (SELECT “ParentEntityId”, “Parent”, “UUID”, “convert_from”(“Payload”, ‘JSON’) AS “Payload”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM “constructiontest”.“constructiontest”.“dbo”.“exhibit_additional”) AS “nested_0”
) nested_0

image
image1884×608 92.9 KB

how to maintain dataset if any schema change in some other source

2

@Vikash_Singh

Try running queries in this order

SELECT “ParentEntityId”, “Parent”, “UUID”, “convert_from”(“Payload”, ‘JSON’) AS “Payload”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM “constructiontest”.“constructiontest”.“dbo”.“exhibit_additional”

SELECT “ParentEntityId”, “Parent”, “UUID”, “Payload”,“nested_0”.“Payload”[’’] AS “newadditionalinspectionitem”, “nested_0”.“Payload”[‘additionalinspectionitem’] AS “additionalinspectionitem”, “nested_0”.“Payload”[‘treeStatus’] AS “treeStatus”, “nested_0”.“Payload”[‘additionalinspectionitemvisualinspection’] AS “additionalinspectionitemvisualinspection”, “nested_0”.“Payload”[‘failadditionalinspectionitemvisualinspectioncorrected’] AS “failadditionalinspectionitemvisualinspectioncorrected”, “nested_0”.“Payload”[‘additionalinspectioncomments’] AS “additionalinspectioncomments”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM (SELECT “ParentEntityId”, “Parent”, “UUID”, “convert_from”(“Payload”, ‘JSON’) AS “Payload”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM “constructiontest”.“constructiontest”.“dbo”.“exhibit_additional”) AS “nested_0”

SELECT ParentEntityId,‘null’ AS new_field, Parent, UUID, Payload, newadditionalinspectionitem,additionalinspectionitem, treeStatus, additionalinspectionitemvisualinspection, CAST(failadditionalinspectionitemvisualinspectioncorrected as VARCHAR(2048)) AS failadditionalinspectionitemvisualinspectioncorrected, additionalinspectioncomments, Type, EntityTimeZone, CreatedBy, ModifiedBy, ModifiedDateTime, CreatedDateTime, SyncDateTime, CreatedByUserId, ModifiedByUserId
FROM (
SELECT “ParentEntityId”, “Parent”, “UUID”, “Payload”,“nested_0”.“Payload”[’’] AS “newadditionalinspectionitem”, “nested_0”.“Payload”[‘additionalinspectionitem’] AS “additionalinspectionitem”, “nested_0”.“Payload”[‘treeStatus’] AS “treeStatus”, “nested_0”.“Payload”[‘additionalinspectionitemvisualinspection’] AS “additionalinspectionitemvisualinspection”, “nested_0”.“Payload”[‘failadditionalinspectionitemvisualinspectioncorrected’] AS “failadditionalinspectionitemvisualinspectioncorrected”, “nested_0”.“Payload”[‘additionalinspectioncomments’] AS “additionalinspectioncomments”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM (SELECT “ParentEntityId”, “Parent”, “UUID”, “convert_from”(“Payload”, ‘JSON’) AS “Payload”, “Type”, “EntityTimeZone”, “CreatedBy”, “ModifiedBy”, “ModifiedDateTime”, “CreatedDateTime”, “SyncDateTime”, “CreatedByUserId”, “ModifiedByUserId”
FROM “constructiontest”.“constructiontest”.“dbo”.“exhibit_additional”) AS “nested_0”
) nested_0

Getting same error?

3

have triyed both query but not able to query newly field same error

image
image1911×642 65.3 KB

4

i have scenario where Payload Column may update schema as well as add new field for eg:

dataset created from select query on Parent DB where field exist

{“additionalinspectionitem”:“create additinal visual inspection”,“treeStatus”:“collapsed”,“additionalinspectionitemvisualinspection”:“Pass”}

now we have added new field
{“extraspectionitem”:“extra additinal visual inspection”,“formStatus”:“collapsed”,“additionalinspectionitemvisualinspection”:“Pass”}

Now how i will query so that new field which added in schema will create NULL or empty column so that later when some record exist in DB we will refresh metadata

5

@Vikash_Singh

Is Dremio ignoring the column as all values are empty?