google-bigquery

Extract Dynamic keys from json in BQ

I have a json like :

"z": {"12345": "xyz", "56789": "abc", "23456": "jkl"},
"z": {"12345": "xyz", "56789": "abc"}

where the keys are dynamic.

I want to extract the keys as well as their values and also the number of keys are not fixed in z. It can be 1/2/3/4/5 or even null. I have looked into other threads but they're different use cases. How to extract this via JSON_EXTRACT ?

Solution

  • Consider below example

    select id, key, value
from your_table, unnest([struct(json_extract(json, '$.z') as z)]),
unnest(bqutil.fn.json_extract_keys(z)) key with offset
join unnest(bqutil.fn.json_extract_values(z)) value with offset
using (offset)

    If apply to sample data from your question

    with your_table as (
  select 1 id, '{"z": {"12345": "xyz", "56789": "abc", "23456": "jkl"}}' json union all
  select 2, '{"z": {"12345": "xyz", "56789": "abc"}}'
)

    the output is

    enter image description here

    My project is in europe-west83 location. can you suggest any workaround for this?

    Just create those functions for yourself. Or use temp functions ...

    create temp function json_extract_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));""";
create temp function json_extract_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));""";
select id, key, value
from your_table, unnest([struct(json_extract(json, '$.z') as z)]),
unnest(json_extract_keys(z)) key with offset
join unnest(json_extract_values(z)) value with offset
using (offset)