BigQuery Dynamic JSON attributes as columnar data

user_3850095 0
Viewed 149

I have a table with one of the columns containing JSON.

Col_A Col_B Col_C
1 Abc1 {“a”: “a_val”, “b”: “b_val”}
2 Abc2 {"a": “a_val2”, “c”: “c_val2”}
3 Abc3 {"b": “b_val3”, “c”: “c_val3”, “d”: {“x”: “x_val”, “y”: “y_val”}}

How can I put together BQ SQL to extract the columns and attributes of the JSON as additional columns. Need to go only 1 level deep into the JSON. So the output should look like:

Col_A Col_B A B C D
1 Abc1 a_val b_val
2 Abc2 a_val2 c_val2
3 Abc3 b_val3 c_val3 {“x”: “x_val”, “y”: “y_val”}
google-bigquery sql json
1 Answers

Consider below approach

create temp function json_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
""";
create temp function json_path(json string, json_path string)
returns string
language js as """
    try { var parsed = JSON.parse(json);
        return JSON.stringify(jsonPath(parsed, json_path));
    } catch (e) { return null }
"""
OPTIONS (
    library="gs://my-storage/jsonpath-0.8.0.js"
);
select * from (
  select t.* except(col_c), key, trim(json_path(col_c, '$.' || key), '"[]') value
  from your_table t,
  unnest(json_keys(col_c)) key 
)
pivot (min(value) for key in ('a', 'b', 'c', 'd'))

if applied to sample data in your question

with your_table as (
  select 1 col_a, 'abc1' col_b, '{"a": "a_val", "b": "b_val"}' col_c union all
  select 2, 'abc2', '{"a": "a_val2", "c": "c_val2"}' union all
  select 3, 'abc3', '{"b": "b_val3", "c": "c_val3", "d": {"x": "x_val", "y": "y_val"}}' 
)

output is

enter image description here

In order to use above you need to upload jsonpath-0.8.0.js (can be download at https://code.google.com/archive/p/jsonpath/downloads) into your GCS bucket gs://my-storage/

As you can see - above solution assumes you know key names in advance
Obviously, when you know keys in advance you would simply use json_extract, but this would not work for when you don't know keys in advance!
So, if you don't know - above solution can be used as a template for dynamically generating query (with real keys in for key in ('a', 'b', 'c', 'd')) to be executed with EXECUTE IMMEDIATE

user_52219440
Related