BQ中替代NTH函数，按变量名/ID提取嵌套数组元素的方案问询

Great question! I’ve run into this exact scenario with nested key-value arrays in BigQuery before—NTH() is totally useless here because it depends on fixed positions, which breaks when rows have different keys or out-of-order elements. Here are a few clean, scalable approaches to solve this:

Extract a Specific Variable by Name #

If you just need to pull one or a few specific variables from the nested array, use a scalar subquery with UNNEST(). This lets you target keys directly, regardless of their position in the array:

SELECT
  user_id,
  -- Grab the value for the 'page_type' key; returns NULL if the key doesn't exist
  (SELECT value FROM UNNEST(customDimensions) WHERE key = 'page_type') AS page_type,
  -- Repeat for other keys you need
  (SELECT value FROM UNNEST(customDimensions) WHERE key = 'user_segment') AS user_segment
FROM
  your_project.your_dataset.your_table

This is way more reliable than NTH() because it doesn’t care about array order—you’re explicitly matching the key name.

Pivot Key-Value Arrays into a Wide Table #

If you want to turn the nested key-value pairs into individual columns (a "wide" table), use conditional aggregation with UNNEST():

SELECT
  user_id,
  -- For each key, use MAX(IF(...)) to grab its value (MAX works since each key per row is unique)
  MAX(IF(cd.key = 'page_type', cd.value, NULL)) AS page_type,
  MAX(IF(cd.key = 'user_segment', cd.value, NULL)) AS user_segment,
  MAX(IF(cd.key = 'session_duration', cd.value, NULL)) AS session_duration
FROM
  your_project.your_dataset.your_table,
  UNNEST(customDimensions) AS cd
GROUP BY
  user_id

This handles missing keys gracefully (they’ll show up as NULL) and works even if rows have different sets of keys.

Dynamic Pivoting for Unknown/Many Variables #

If you don’t know all the key names upfront or have too many to write manually, use dynamic SQL to generate the pivot columns automatically:

DECLARE pivot_columns STRING;

-- First, grab all unique key names from the array and format them into aggregation clauses
SET pivot_columns = (
  SELECT STRING_AGG(
    DISTINCT CONCAT("MAX(IF(cd.key = '", key, "', cd.value, NULL)) AS ", key),
    ', '
  )
  FROM your_project.your_dataset.your_table, UNNEST(customDimensions) AS cd
);

-- Execute the dynamic pivot query
EXECUTE IMMEDIATE CONCAT('
  SELECT
    user_id,
    ', pivot_columns, '
  FROM
    your_project.your_dataset.your_table,
    UNNEST(customDimensions) AS cd
  GROUP BY
    user_id
');

This will automatically create a column for every unique key in your nested array—no manual coding required.

Note on ARRAY_LOOKUP #

You mentioned BigQuery’s array lookup functionality—while ARRAY_LOOKUP (or the older ARRAY(SELECT ... LIMIT 1) pattern) can grab elements from arrays, it’s still index-based or relies on filtering (which is exactly what we’re doing in the scalar subquery approach above). The key difference here is that we’re filtering on the key name instead of position, which is the right tool for this key-value array scenario.

内容的提问来源于stack exchange，提问作者Paul Schaffer