How to flatten dynamic json key into columns in BigQuery?

user_4177530 0
Viewed 986

Here is the original JSON structure:

{
    "tt": [{
    "box": {
      "type": "A"
    },
        "rr": {...}
    }, {
    "box": {
      "type": "B"
    },
        "rr": {...}
    }, {
    "box": {
      "type": "C"
    },
        "rr": {...}
    }]
}

Then I use the SQL command below to get the tt structure

with t1 as (
SELECT
  rate_card,
  JSON_EXTRACT_ARRAY(rate_card, '$.tt') as rr,
FROM `original_table_temp`
)

The rr JSON structure from the previous JSON structure above looks something like below

{
  "rr": {
    "10": {   <-------------------------
      "tils": {
        "mdd": {
          "df": {
            "tif": {
              "sc": 17.85,  <------------------
              "evr": [
                {
                  "p": 16.35,   <---------------------
                  "t": null,
                  "nr?": false
                }
              ]
        }
      }
    },
    "11": {...},
    "12": {...},
    ...
  }
}

All the SQL commands above are executed in BigQuery.

I am interested to get the box.type, rr's keys, which are 10, 11, 12 & so on in rr into a column.

The columns next to the keys are sc & p

In the end the output of the table would be as below:

+------------+---------+---------------+
| box.type|    rr  |  sc   |     p     |
+------------+-------------------------+
|  A      | 10     | 17.85    |  16.35    |

|  A      | 11     |some value|  ....     |
......

|   C     | 12     |  .....   |     ......|
+------------+----------------------------+

At the moment, I managed to get the sc value by hard-cording in SQL BigQuery. See below:

SELECT
  JSON_EXTRACT_SCALAR(rr_2, '$.rr.10.tils.mdd.df.tif.sc') AS sc,
FROM t1,
UNNEST(rr) rr_2

The method above is not productive & efficient as there are many keys like 10, 11, 12 and so on.

  1. How to extract the dynamic keys from rr into a columns?
  2. How to not hard-code the SQL to get sc & p?

Feel free to remove the <---------- in the JSON sample when you test your answer.

It is a long post. If you need more information, let me know.

Thanks for your patience & time.

unnest google-bigquery sql json
2 Answers

Consider below

execute immediate (
    select string_agg("select " || key || ''' key
    , JSON_EXTRACT_SCALAR(rr_2, '$.rr.''' || key || '''.tils.mdd.df.tif.sc') AS sc 
    , JSON_EXTRACT_SCALAR(rr_2, '$.rr.''' || key || '''.tils.mdd.df.tif.evr[0].p') AS p
  from `project.dataset.table`''', " union all ")
  from `project.dataset.table`, unnest(regexp_extract_all(regexp_replace(JSON_EXTRACT(rr_2, '$.rr'), r':{.*?}+', ''), r'"(.*?)"')) key
);

If to apply to rr JSON structure from your original example - output is

enter image description here

user_52219440

JSON objects with a non-closed set of keys don't map well into a SQL table-oriented domain.

One way to solve this is to transform JSON data structured like {key1: {}, key2: {}, ...} into something like [{key1, ...}, {key2, ...}, ...].

Such a transformation can be readily achieved in the Javascript domain using a BigQuery Javascript UDF...

CREATE TEMPORARY FUNCTION extract_tt(json STRING)
RETURNS STRUCT<
  tt ARRAY<STRUCT<
    box STRUCT<
      type STRING
    >,
    rr ARRAY<STRUCT<
      key STRING,
      tils STRUCT<
        mdd STRUCT<
          df STRUCT<
            tif STRUCT<
              sc FLOAT64,
              evr ARRAY<STRUCT<
                p FLOAT64,
                t STRING,
                nr BOOL
              >>
            >
          >
        >
      >
    >>
  >>
>
DETERMINISTIC
LANGUAGE js
AS
r"""
try {
  return JSON.parse(json,
    (key, value) => {
      if (key === 'rr' && value !== null && typeof value === 'object')
        return Object.keys(value).map(key => ({ key, ...value[key] }));
      else
        return value;
    });
}
catch {}
""";

WITH extracted AS (
  SELECT extract_tt(json).*
  FROM UNNEST([
    '{"tt":[{"box":{"type":"A"},"rr":{"10":{"tils":{"mdd":{"df":{"tif":{"sc":17.85,"evr":[{"p":16.35,"t":null,"nr":false}]}}}}},"11":{}}},{"box":{"type":"B"},"rr":{}},{"box":{"type":"C"},"rr":{"12":{"tils":{"mdd":{"df":{"tif":{"sc":99}}}}}}}]}'
  ]) AS json
)
SELECT box.type, key, tils.mdd.df.tif.sc, tils.mdd.df.tif.evr[OFFSET(0)].p
FROM extracted, UNNEST(tt), UNNEST(rr);

Here is the result of unpacking rr which can smoothly represent any degree of variation on the dynamic keys of rr...

Row type key sc p
1 A 10 17.85 16.35
2 A 11 null null
3 C 12 99.0 null

Note the return type of the UDF was carefully crafted to map from the JSON domain to the SQL domain. As long as the types match, the BigQuery object caster takes care of everything else for you!

user_26608560
Related