CODEWITHSUNDEEP
jsongoogle-bigqueryunnestjson-extract
Nanda
Nanda

Reputation: 189

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.

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:

expected output

Upvotes: 0

Views: 73

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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

Upvotes: 1

Related Questions