Как создать массив вложенных полей и массивов в BigQuery

Question

Я пытаюсь создать таблицу в BigQuery в соответствии со схемой json, которую я добавлю в GCS и оттуда перенесу в паб / подраздел. Мне нужно создать несколько массивов и вложенных полей, чтобы добиться этого.

Используя struct и array_agg, я могу получить массивы struct, но не могу понять, как создать структуру массива.

Представьте, что у меня есть схема JSON, как показано ниже:

{
  "vacancies": {
    "id": "12",
    "timestamp": "2019-08-22T04:04:26Z",
    "version": "1.0",
    "positionOpening": {
      "documentId": {
        "value": "505"
      },
      "statusCode": "Closed",
      "registrationDate": "2014-05-07T16:11:22Z",
      "lastUpdated": "2014-05-07T16:14:56Z",
      "positionProfiles": [
        {
        "positionTitle": "Data Scientist for international company",
        "positionQualifications": [
          {
            "experienceSummary": [
              {"measure": {"value": "10","unitCode": "ANN"}},
              {"measure": {"value": "4","unitCode": "ANN"}}
            ],
            "educationRequirement": {
              "programs": ["Physics","Computer Science"],
              "programConcentrations": ["Data Analysis","Python Programming"]
            },
            "languageRequirement": [
              {
                "competencyName": "English",
                "requiredProficiencyLevel": {"scoresNumeric": [{"value": "100"},{"value": "95"}]}
              },
              {
                "competencyName": "French",
                "requiredProficiencyLevel": {"scoresNumeric": [{"value": "95"},{"value": "70"}]}
              }
            ]
          }
        ]
        }
      ]
    }
  }
}

Как я могу создать запрос SQL, чтобы получить это в результате?

Заранее спасибо за помощь!

Answer 1

Для этого вам, возможно, придется создать временную таблицу.

Этот первый оператор создания преобразует денормализованную таблицу в таблицу с массивом структур.

Второй оператор create возьмет эту временную таблицу и вставит массив в (массив) структуру (ы).

Вы можете удалить внутреннюю структуру из первого запроса и обернуть второй запрос массивом, чтобы построить строгую структуру массивов. Но это должно быть достаточно гибким, чтобы вы могли создавать массив структур, структуру массивов или любую их комбинацию столько раз, сколько захотите, вплоть до 15 уровней, на которых BigQuery позволяет вам максимально использовать их.

Конечным результатом этого может быть таблица с одним столбцом (column1) стандартного типа данных, а также массив структур под названием OutsideArrayOfStructs. Эта структура имеет два столбца "стандартных" типов данных, а также массив структур, называемых InsideArrayOfStructs.

 CREATE OR REPLACE TABLE dataset.tempTable as (
     select 
         column1,
         column2,
         column3,
         ARRAY_AGG(
                 STRUCT(
                     ArrayObjectColumn1,
                     ArrayObjectColumn2,
                     ArrayObjectColumn3
                 )
             ) as InsideArrayOfStructs
     FROM
         sourceDataset.sourceTable
     GROUP BY 
         column1,
         column2,
         column3 )

 CREATE OR REPLACE TABLE dataset.finalTable as (
     select 
         column1,
         ARRAY_AGG(
                 STRUCT(
                     column2,
                     column3,
                     InsideArrayOfStructs
                 )
        ) as OutsideArrayOfStructs
     FROM
         dataset.tempTable
     GROUP BY
         Column1 )