Таблица запросов с 2 повторяющимися полями

Question

У меня есть схема, которая содержит несколько повторяющихся полей, которые все типа записи. Например, схема выглядит так:

name: string,

class: record(repeated),

class.id :string

class.title: string,

school: record(repeated),

school.id: string,

school.title: string.

Как я могу запросить в стандартном SQL и вернуть строку ученика со списком класса и школы (класс и школа совершенно не связаны).

На самом деле, простой SELECT * работает, но я хочу выбрать каждый столбец и переименовать их, то есть SELECT name как имя_стадента, классы, школы. Я также хочу отфильтровать некоторые классы и школы, то есть показывать только те классы, которые удовлетворяют некоторым ограничениям.

Пример: имя: 'Томь', класс: [{id: 1, название: математика}, {id: 2, название: физика}, {id: 3, название: математика}], школа: [{id: 1, title: Yale}, {id: 2, title: Havard}, {id: 3, title: Yale}] Я хочу отфильтровать по class.title и school.title, чтобы получить такую ​​строку: name: 'Tom', класс: [{id: 1, title: math}, {id: 3, title: math}], школа: [{id: 1, title: Yale}, {id: 3, title: Yale}]

Answer 1

Ниже для BigQuery Standard SQL

#standardSQL
SELECT name as student_name, 
  ARRAY(SELECT AS STRUCT * FROM UNNEST(class) class WHERE class.title = 'math') classes,
  ARRAY(SELECT AS STRUCT * FROM UNNEST(school) school WHERE school.title = 'Yale') schools
FROM `yourproject.yourdataset.yourtable`  

Вы можете проверить / поиграть с вышеупомянутым, используя фиктивные данные из вашего вопроса:

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT 
    'Tom' name, 
    [
      STRUCT(1 AS id, 'math' AS title), 
      (2, 'physics'), 
      (3, 'math')
    ] class, 
    [
      STRUCT(1 AS id, 'Yale' AS title), 
      (2, 'Havard'), 
      (3, 'Yale')
    ] school 
)
SELECT name as student_name, 
  ARRAY(SELECT AS STRUCT * FROM UNNEST(class) class WHERE class.title = 'math') classes,
  ARRAY(SELECT AS STRUCT * FROM UNNEST(school) school WHERE school.title = 'Yale') schools
FROM `yourproject.yourdataset.yourtable`   

с результатом, как показано ниже

name    class.id    class.title school.id   school.title     
Tom     1           math        1           Yale     
        3           math        3           Yale     

Выше должно дать вам представление о том, как фильтровать внутри повторяющегося поля