Große Abfrage - Transponieren Sie Array- / JSON-Objekte in Spalten

Question

Diese Frage ist eine Fortsetzung dieser beiden:

1. Große Abfrage - Transponieren Sie Arrays in Spalten
2. Große Abfrage - Transponieren Sie bestimmte Felder in Spalten

Wir haben eine Tabelle in Big Query wie unten.

Eingabetabelle:

 Name | Question  | Answer
 -----+-----------+-------
 Bob  | Interest  | ["a"]     
 Sue  | Interest  | ["a", "b"]
 Joe  | Interest  | ["b"]
 Joe  | Gender    | Male
 Bob  | Gender    | Female
 Sue  | DOB       | 2020-10-17
 Bob  | Others    | { "country" : "es", "language" : "ca"}

Hinweis: Alle Werte in der Spalte Antwort sind Zeichenfolgenwerte und die Arrays / JSON-Objekte sind dynamisch.

Wir möchten die obige Tabelle in das folgende Format konvertieren, um sie BI / Visualization-freundlich zu machen.

Gewünschte Tabelle:

 +-------------------------------------------------------------+
 | Name | a | b | c | Gender | DOB        | country | language |
 +-------------------------------------------------------------+
 | Bob  | 1 | 0 | 0 | Female | 2020-10-17 |   es    |   ca     |
 | Sue  | 1 | 1 | 0 |   -    |     -      |   -     |   -      |
 | Joe  | 0 | 1 | 0 |  Male  |     -      |   -     |   -      |
 +-------------------------------------------------------------+

Answer 1

Unten finden Sie Informationen zu BigQuery Standard SQL

#standardSQL
create temp table data as
select name, question, value as answer 
from `project.dataset.table`, 
unnest(split(translate(answer, '[]" ', ''))) value
where question = 'Interest'
union all
select name, question, answer 
from `project.dataset.table`
where not question in ('Interest', 'Others')
union all
select name, 
  split(value, ':')[offset(0)] as question, 
  split(value, ':')[offset(1)] as answer 
from `project.dataset.table`, 
unnest(split(translate(answer, '{}" ', ''))) value
where question = 'Others';

EXECUTE IMMEDIATE (
  SELECT """
    SELECT name, """ || STRING_AGG("""MAX(IF(answer = '""" || value || """', 1, 0)) AS """ || value, ', ')   
FROM (
  SELECT DISTINCT answer value FROM data
  WHERE question = 'Interest' ORDER BY value
)) || (
  SELECT ", " || STRING_AGG("""MAX(IF(question = '""" || value || """', answer, '-')) AS """ || value, ', ')   
FROM (
    SELECT DISTINCT question value FROM data
    WHERE question != 'Interest' ORDER BY value
)) || """  
  FROM data 
  GROUP BY name
  """;     

wenn Sie sich für Beispieldaten aus Ihrer Frage bewerben möchten

with `project.dataset.table` AS (
  select 'Bob' name, 'Interest' question, '["a"]' answer union all
  select 'Sue', 'Interest', '["a", "b"]' union all
  select 'Joe', 'Interest', '["b"]' union all
  select 'Joe', 'Gender', 'Male' union all
  select 'Bob', 'Gender', 'Female' union all
  select 'Sue', 'DOB', '2020-10-17' union all
  select 'Bob', 'Others', '{ "country" : "es", "language" : "ca"}' 
)    

die Ausgabe ist

Hinweis: Ein EXECUTE IMMEDIATETeil des obigen Skripts ist genau der gleiche wie im vorherigen Beitrag. Die Änderung besteht nur darin, die Originaldaten in die temporäre Tabelle vorzubereiten dataund sie dann zu verwendenEXECUTE IMMEDIATE