[BigQuery] JSON Technique: Replace Dynamic Keys with a Dummy Value

The other day, I was working with JSON-type data in BigQuery and ran into trouble extracting values when the keys were all different.

I was able to solve the problem by referring to the article below, using an approach that replaces the keys with a dummy string before processing.

maxhalford.github.io

For example, consider a case where the keys are user IDs and the values are user names. With a conventional approach, you can't extract a user name without knowing the user ID in advance.

Here, by first replacing the user ID with the string __KEY__ and then specifying the JSON path as $.__KEY__, you can extract the user name.

WITH sample AS (
  SELECT JSON '{"8901": "Taro Yamada", "8071": "Jiro Suzuki"}' AS json_data
)
SELECT
  JSON_VALUE(
    REGEXP_REPLACE(
      TO_JSON_STRING(json_data),
      CONCAT('"', key, '"'),
      '"__KEY__"'
    ),
    '$.__KEY__'
  ) AS value
FROM sample,
UNNEST(JSON_KEYS(json_data)) AS key

Converting JSON-type data to a string and then replacing the keys inside it with a dummy string feels a bit hacky, but there are plenty of situations where there's no other way, so it might be worth knowing.