Я пытаюсь прочитать приведенную ниже структуру JSON. Смог получить данные на 1 уровень и правильно увидеть их чтение, но когда я углубляюсь в другие разделы, он не читает данные правильно. Ниже приведены входные данные, а также код, который я использую, и выходные данные для вашего прочтения. Попросите вашу помощь с этим.
ВВОДНЫЕ ДАННЫЕ:
}, {"ComboId": "5970", "Demands": {"DownHoleTools": {"PrimaryTools": [
] }, "SurfaceTools": { "PrimaryTools": [ { "ToolCode": "19153", "ToolDescription": "MDT_Surface Eqpt", "IsEdoApplicable": false, "Source": "A", "DemandDurationInfo": { "StartDate": "2020-02-12T15:18:32", "EndDate": "2020-02-13T15:18:32" }, "HashNumber": 1, "ClassificationName": "MDT_Surface Eqpt", "ClassificationType": 1, "GroupInfo": { "Code": "965", "Description": "MDT Accessories" }, "CategoryInfo": { "Code": "965", "Description": "MDT Accessories" }, "Comments": "", "IsDeleted": false, "PartNumber": "", "Description": "MDT_Surface Eqpt", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "1" }, { "ToolCode": "19153", "ToolDescription": "MDT_Surface Eqpt", "IsEdoApplicable": false, "Source": "A", "DemandDurationInfo": { "StartDate": "2020-02-12T15:18:32", "EndDate": "2020-02-13T15:18:32" }, "HashNumber": 2, "ClassificationName": "MDT_Surface Eqpt", "ClassificationType": 1, "GroupInfo": { "Code": "965", "Description": "MDT Accessories" }, "CategoryInfo": { "Code": "965", "Description": "MDT Accessories" }, "Comments": "", "IsDeleted": false, "PartNumber": "", "Description": "MDT_Surface Eqpt", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "1" } ], "BackupTools": [ ] }, "Techniques": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Services": [ ], "Tools": "" }, "ComboType": 2, "HashCode": "", "SequenceNumber": "", "ConveyanceInfo": "", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "98e9418f-e50a-417b-affb-5fc4c1f71f39"}, {"ComboId": "5971", "Demands": {"DownHoleTools": {"PrimaryTools": [
], "BackupTools": [ ] }, "SurfaceTools": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Techniques": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Services": [ ], "Tools": "" }, "ComboType": 1, "HashCode": "", "SequenceNumber": "", "ConveyanceInfo": "", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "944cf025-2a8c-4372-9f87-6c80c844ac68"}, {"ComboId": "26793", "Demands": {"DownHoleTools": {"PrimaryTools": [
], "BackupTools": [ ] }, "SurfaceTools": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Techniques": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Services": [ ], "Tools": "" }, "ComboType": 0, "HashCode": "", "SequenceNumber": "", "ConveyanceInfo": "", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "0a9338b2-aa95-4d5a-8e57-1305e78fec0c"}], «CreatedDate»: «2019-09-09T13: 12: 14.94», «CreatedBy»: «VHiremath», «LastModifiedDate»: «2020-02-27T07: 40: 08.071», «LastModifiedBy»: «VPanath» , "Id": "5d764fae3d6a351088a1c9d3"}
], "BackupTools": [ ] }, "SurfaceTools": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Techniques": { "PrimaryTools": [ ], "BackupTools": [ ] }, "Services": [ { "Code": "GIWS", "Name": "Grease Injection WHE Service", "Description": "", "GroupInfos": "", "ClassificationType": 4, "LegacySystemMapping": [ { "LegacyId": "EE61B186-CE57-46A8-B280-FE913CC8FF33", "LegacySystemMappedProperty": "Grease Injection WHE Service", "LegacySystemName": "ODM" }, { "LegacyId": "2e4b1aea-3d91-43b6-9c32-a165a546ed39", "LegacySystemMappedProperty": "Grease Injection WHE Service", "LegacySystemName": "OSCompliance" } ], "Source": "A", "Comments": "", "Id": "9c405cc1-5231-4baf-864f-7974bb4fbe07" }, { "Code": "SCNNGWS", "Name": "Slick Cable Non-Grease Injection WHE Service", "Description": "", "GroupInfos": "", "ClassificationType": 4, "LegacySystemMapping": [ { "LegacyId": "7B41D3DD-A6A7-47AF-81CD-5D8C248183B5", "LegacySystemMappedProperty": "Slick Cable Non-Grease Injection WHE Service", "LegacySystemName": "ODM" }, { "LegacyId": "399e61ed-353a-404f-aad3-1e84c46cb273", "LegacySystemMappedProperty": "Slick Cable Non-Grease Injection WHE Service", "LegacySystemName": "OSCompliance" } ], "Source": "A", "Comments": "", "Id": "0a4848d3-7d93-4721-a556-2ecf1b0a7f43" }, { "Code": "TPWS", "Name": "Third Party WHE Service", "Description": "", "GroupInfos": "", "ClassificationType": 4, "LegacySystemMapping": [ { "LegacyId": "CAF5754A-3F90-40C6-82AC-0A4F484A4E74", "LegacySystemMappedProperty": "Third Party WHE Service", "LegacySystemName": "ODM" }, { "LegacyId": "50ff1668-0b3b-489c-a7ad-794e327028e5", "LegacySystemMappedProperty": "Third Party WHE Service", "LegacySystemName": "OSCompliance" } ], "Source": "A", "Comments": "", "Id": "ee923f9e-210f-4c36-8fc4-f1e9521d0cbe" }, { "Code": "WLPPS", "Name": "Wireline Low Pressure Packoff Service", "Description": "", "GroupInfos": "", "ClassificationType": 4, "LegacySystemMapping": [ { "LegacyId": "CB79CDE1-2271-418D-9E67-D4B07E94AC61", "LegacySystemMappedProperty": "Wireline Low Pressure Packoff Service", "LegacySystemName": "ODM" }, { "LegacyId": "514650dd-5b71-4889-b991-0d77dd355666", "LegacySystemMappedProperty": "Wireline Low Pressure Packoff Service", "LegacySystemName": "OSCompliance" } ], "Source": "A", "Comments": "", "Id": "2216c1c0-41ba-40aa-90c2-1096e8191d2c" } ], "Tools": "" }, "ComboType": 2, "HashCode": "", "SequenceNumber": "", "ConveyanceInfo": "", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "446d43e1-6476-4408-b850-e5f233933ba9"Код:
Ожидаемый результат:
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
var result = jsonPath(JSON.parse(json), json_path);
if(result){return result;}
else {return [];}
"""
OPTIONS (
library="gs://temp-dev-workspace/json_temp/jsonpath-0.8.0.js"
);
SELECT job_id,
combo_id,
tool_code
from temp.dbm_eqp_data,
UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.Combos[*].ComboId')) combo_id,
UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.Combos[*].Demands.DownHoleTools.PrimaryTools[*].ToolCode')) tool_code
;
Ниже приведен стандартный SQL BigQuery.
