struct to entries UDF

_toEntriesUDF.sql

CREATE TEMP FUNCTION
  json_to_entries_impl(json STRING)
  RETURNS ARRAY<STRUCT<key STRING,
  path ARRAY<STRING>,
  type STRING,
  jsonValue STRING,
  booleanValue BOOL,
  numberValue FLOAT64,
  stringValue STRING,
  bigIntValue STRING >>
  LANGUAGE js AS """
function toEntriesImpl(obj, parent) {
    return Object.entries(obj).flatMap(([key, value]) => {
        const type = value == null ? "null" : (Array.isArray(value) ? "array" : typeof value)
        const path = parent.concat(key)
        let bigInt = undefined;
        try {
            bigInt = BigInt(value)
        } catch(e) {
        }
        return [{
            type: type,
            key: key,
            path: path,
            jsonValue: JSON.stringify(value),
            booleanValue: typeof value === "boolean" ? value : null,
            numberValue: typeof value === "number" ? value : null,
            stringValue: typeof value === "string" ? value : null,
            bigIntValue: bigInt,
        }, ...((type === "array" || type === "object")?toEntriesImpl(value, path):[])]
    })
}
const result = toEntriesImpl(JSON.parse(json), [])
return result;
""";
CREATE TEMP FUNCTION
  json_to_entries(json STRING) AS ( ARRAY((
      SELECT
        AS STRUCT * REPLACE(
        IF
          (dateValue IS NULL,
            datetimeValue,
            NULL) AS datetimeValue,
        IF
          (datetimeValue IS NULL,
            timestampValue,
            NULL) AS timestampValue),
        CASE
          WHEN geographyValue IS NOT NULL THEN "GEOGRAPHY"
          WHEN dateValue IS NOT NULL THEN "DATE"
          WHEN datetimeValue IS NOT NULL THEN "DATETIME"
          WHEN timestampValue IS NOT NULL THEN "TIMESTAMP"
          WHEN timeValue IS NOT NULL THEN "TIME"
          WHEN binaryValue IS NOT NULL THEN "BINARY"
          WHEN bigIntValue IS NOT NULL THEN "NUMERIC"
          WHEN type = "number" THEN "FLOAT64"
          WHEN type = "string" THEN "STRING"
          WHEN type = "array" THEN "ARRAY"
          WHEN type = "object" THEN "STRUCT"
          WHEN type = "null" THEN "NULL"
        ELSE
        type
      END
        AS bigqueryType
      FROM (
        SELECT
          * REPLACE(CAST(bigIntValue AS NUMERIC) AS bigIntValue),
          SAFE.FROM_BASE64(stringValue) AS binaryValue,
          SAFE_CAST(stringValue AS DATE) AS dateValue,
          SAFE_CAST(stringValue AS TIME) AS timeValue,
          SAFE_CAST(stringValue AS DATETIME) AS datetimeValue,
          SAFE_CAST(stringValue AS TIMESTAMP) AS timestampValue,
          SAFE.ST_GeogFromText(stringValue) AS geographyValue,
        FROM
          UNNEST(json_to_entries_impl(json))))));
CREATE TEMP FUNCTION
  any_to_entries(obj ANY TYPE) AS (json_to_entries(TO_JSON_STRING(obj)));
SELECT
  any_to_entries((
    SELECT
      AS STRUCT 1 AS i,
      1.5 AS f,
      NUMERIC "1234567890123456789012345" AS d,
      "foobarbaz" AS s,
      ["xyzzy"] AS a,
      b'{"key": "value"}' AS bin,
      CURRENT_DATE() AS date,
      CURRENT_TIME() AS time,
      CURRENT_DATETIME() AS datetime,
      CURRENT_TIMESTAMP() AS timestamp,
      ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))') AS geo,
      TRUE AS b,
      STRUCT<i INT64>(1) AS o,
      NULL AS n )) AS e
UNION ALL
SELECT
  any_to_entries([1,
    2,
    3,
    4]) AS e

References.md

• https://stackoverflow.com/a/49985190

result.png