Map in BigQuery (dynamic keys)

user_1284720 0
Viewed 2349

In bigquery, if we are interested in constructing json output, we can usually use struct for json object when the keys are known beforehand.

SELECT TO_JSON_STRING(STRUCT(key1))
FROM (SELECT "val1" as key1 UNION ALL
      SELECT "val2" as key1)


Result
{"key1":"val1"}
{"key1":"val2"}

But in the case where the keys are dynamic, we really want a map type, similar to the avro map type

For example

SELECT *
FROM (SELECT "key1" as key, "val1" as val UNION ALL
      SELECT "key2" as key, "val2" as val)

should return
{"key1": "val1", "key2": "val2"}

is there anyway to achieve this using BigQuery SQL?

google-bigquery
2 Answers

Below is for BigQuery Standard SQL

Something simple like below should produce expected result

#standardSQL
WITH `project.dataset.table` AS (
  SELECT "key1" AS key, "val1" AS val UNION ALL
  SELECT "key2" AS key, "val2" AS val
)
SELECT '{' || STRING_AGG(REPLACE(TRIM(FORMAT('%T', t), '()'), '", "', '": "'), ', ') || '}' AS return
FROM `project.dataset.table` t

with output

Row return   
1   {"key1": "val1", "key2": "val2"}
user_52219440

You can use Dynamic SQL to generate JSON string:

DECLARE
  JSONSTR STRING;
SET
  JSONSTR = (
  SELECT
    '{' || STRING_AGG('"' || key || '": "' || val || '"', ', ') || '}'
  FROM (
    SELECT *
    FROM (SELECT "key1" AS key, "val1" AS val
          UNION ALL
          SELECT "key2" AS key, "val2" AS val)));
EXECUTE IMMEDIATE
  FORMAT("""SELECT '%t'""",JSONSTR);
user_140485790
Related