Reading Multiple levels of repeated JSON structure data in Google BigQuery
I am trying to read the below JSON structure. Was able to get the data to 1 level and see its reading Correctly, but when I go deeper into the other sections.its not reading data correctly. Below is the Input data and the Code Iam using and Output for your perusal. Request your help with this.
INPUT DATA:
{ "ProjectId": "P.2000002", "OperationId": "O.2000002.01", "ActivityId": "A.2000002.01.01", "Description": "", "Combos": [ { "ComboId": "9146", "Demands": { "DownHoleTools": { "PrimaryTools": [ { "ToolCode": "19139", "ToolDescription": "VSIT-C/D", "IsEdoApplicable": true, "Source": "A", "DemandDurationInfo": { "StartDate": "2019-09-09T17:42:10", "EndDate": "2019-09-19T23:00:00" }, "HashNumber": 1, "ClassificationName": "VSIT-C/D", "ClassificationType": 0, "GroupInfo": { "Code": "1519", "Description": "VSIT" }, "CategoryInfo": { "Code": "1519", "Description": "VSIT" }, "Comments": "", "IsDeleted": false, "PartNumber": "", "Description": "", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "1" }, { "ToolCode": "7030", "ToolDescription": "VSIB-P", "IsEdoApplicable": false, "Source": "A", "DemandDurationInfo": { "StartDate": "2019-09-09T17:42:12", "EndDate": "2019-09-19T23:00:00" }, "HashNumber": 1, "ClassificationName": "VSIB-P", "ClassificationType": 0, "GroupInfo": { "Code": "1519", "Description": "VSIT" }, "CategoryInfo": { "Code": "1519", "Description": "VSIT" }, "Comments": "", "IsDeleted": false, "PartNumber": "", "Description": "", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "1" }, { "ToolCode": "3707", "ToolDescription": "HILT-TLD-H", "IsEdoApplicable": false, "Source": "A", "DemandDurationInfo": { "StartDate": "2020-02-12T15:18:32", "EndDate": "2020-02-13T15:18:32" }, "HashNumber": 1, "ClassificationName": "HILT-TLD-H", "ClassificationType": 0, "GroupInfo": { "Code": "842", "Description": "HILT" }, "CategoryInfo": { "Code": "842", "Description": "HILT" }, "Comments": "", "IsDeleted": false, "PartNumber": "", "Description": "HILT-TLD-H", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "1" }, { "ToolCode": "3707", "ToolDescription": "HILT-TLD-H", "IsEdoApplicable": false, "Source": "A", "DemandDurationInfo": { "StartDate": "2020-02-12T15:18:32", "EndDate": "2020-02-13T15:18:32" }, "HashNumber": 2, "ClassificationName": "HILT-TLD-H", "ClassificationType": 0, "GroupInfo": { "Code": "842", "Description": "HILT" }, "CategoryInfo": { "Code": "842", "Description": "HILT" }, "Comments": "", "IsDeleted": false, "PartNumber": "", "Description": "HILT-TLD-H", "CreatedDate": "0001-01-01T00:00:00", "CreatedBy": "", "LastModifiedDate": "0001-01-01T00:00:00", "LastModifiedBy": "", "Id": "1" } ], "BackupTools": [
] }, "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": "5970", "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": "5971", "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"}, { "ComboId": "26793", "Demands": { "DownHoleTools": { "PrimaryTools": [
], "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"} ], "CreatedDate": "2019-09-09T13:12:14.94", "CreatedBy": "VHiremath", "LastModifiedDate": "2020-02-27T07:40:08.071", "LastModifiedBy": "VPanath", "Id": "5d764fae3d6a351088a1c9d3" }
Code:
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
;
Expected Output:
Below is for BigQuery Standard SQL
#standardSQL
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
LEFT JOIN UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.Combos[?(@.ComboId=="' || combo_id || '")].Demands.DownHoleTools.PrimaryTools[*].ToolCode')) tool_code
If to apply to sample data from your question - output is
Row job_id combo_id tool_code
1 1 9146 19139
2 1 9146 7030
3 1 9146 3707
4 1 9146 3707
5 1 5970 null
6 1 5971 null
7 1 26793 null
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices