Блог

У меня есть следующая структура таблицы:

 |       Field name         |    Type    |    Mode    |
-----------------------------------------------------
message_info               |   RECORD   |  NULLABLE  |
  |-destination            |   RECORD   |  REPEATED  |
      |-address            |   STRING   |  NULLABLE  |
      |-service            |   STRING   |  NULLABLE  |
      |-selector           |   STRING   |  NULLABLE  |
      |-smime_signature    |   STRING   |  NULLABLE  |
      |-smime_decryption   |   STRING   |  NULLABLE  |
      |-smime_parsing      |   STRING   |  NULLABLE  |
      |-smime_extraction   |   STRING   |  NULLABLE  |  
 
 

Я хочу сохранить RECORD REPEATED природу и из destination поля, но я хочу получить только первые три вложенных поля, так как мне не нужны поля smime.

Я попробовал следующее:

 SELECT
    STRUCT(
        d.address AS address,
        d.service AS service,
        d.selector AS selector
    ) AS destination
FROM
    `myproject.mydataset.mytable` AS mail,
    UNNEST(mail.message_info.destination) AS d
 

Однако это не сохраняет REPEATED природу message_info.destination поля. Если я добавлю такое ARRAY_AGG() утверждение, как это:

 SELECT
    ARRAY_AGG(STRUCT(
        d.address AS address,
        d.service AS service,
        d.selector AS selector
    )) AS destination
FROM
    `myproject.mydataset.mytable` AS mail,
    UNNEST(mail.message_info.destination) AS d
 

Я получаю сообщение об ошибке, в котором говорится, что оно конфликтует с другими не повторяющимися полями, которые я извлекаю: SELECT list expression references mail.event_info.timestamp_usec which is neither grouped nor aggregated .

Каков правильный способ извлечения этих полей?

Вы можете использовать подзапрос:

 select mail.*,
       (select array_agg(struct(d.address AS address,
                                d.service AS service,
                                d.selector AS selector
                               )
                        )
        from unnest(mail.message_info.destination) d
       ) as new_destination
from `myproject.mydataset.mytable` mail;
 

Это создает new_destination для каждой строки в исходной таблице. Когда вы используете array_agg() во внешнем запросе без агрегирования, вы агрегируете все строки, поэтому вы не можете выбрать другие столбцы.

Рассмотрим следующий подход.
В то время как подходы в других ответах разрушают схему исходной таблицы — этот полностью сохраняет ее

 select * replace(
    (select as struct * 
       replace(array(select as struct address, service, selector from mi.destination) as destination)
     from unnest([message_info]) mi
    ) as message_info
  ) 
from `myproject.mydataset.mytable` t