Привет Герхард, это здорово!

Привет Герхард, это здорово! Я не об использовании ROW_NUMBER, но это действительно работает :). Очень полезно, если вы хотите иметь такой запрос в представлении!

У меня есть два дополнительных предложения:
- порядок score должен быть нисходящим, так как нас интересует вторая по достоверности метка
- подзапросы в предложениях ARRAY могут быть FROM полями массива напрямую без UNNEST -ing, но вам нужно псевдоним исходной таблицы

Запрос становится таким (предложения выделены жирным шрифтом):

WITH scores_and_data AS (
   SELECT
      data.object_id,
      ARRAY(
         SELECT AS STRUCT
            score,
            description,
            ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
         FROM
            data.labelAnnotations
      ) AS scores
   FROM
      `bigquery-public-data.the_met.vision_api_data` data
)
SELECT
   * EXCEPT (scores),
   ARRAY(
      SELECT AS STRUCT
         * EXCEPT(rn)
      FROM
         data.scores
   ) AS labelAnnotations,
   ARRAY(
      SELECT AS STRUCT
         * EXCEPT (rn)
      FROM
         data.scores
      WHERE
         rn = 2
   ) AS second_most_confident_labelAnnotation
FROM
   scores_and_data data

Спасибо!

EDIT:После некоторого исследования задача может быть выполнена довольно просто с помощью команды OFFSET :

SELECT
  object_Id,
  ARRAY(
    SELECT AS STRUCT
      score,
      description
    FROM
      data.labelAnnotations
    ORDER BY score DESC
  )[OFFSET(1)]  AS second_most_confident_labelAnnotation
FROM
  `bigquery-public-data.the_met.vision_api_data` data
WHERE ARRAY_LENGTH(data.labelAnnotations)>1