Big Query - Transpose des objets array / json en colonnes

Question

Cette question est une continuation de ces deux:

1. Big Query - Transposer des tableaux en colonnes
2. Big Query - Transposer des champs spécifiques en colonnes

Nous avons un tableau dans Big Query comme ci-dessous.

Tableau d'entrée:

 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"}

Remarque: toutes les valeurs de la colonne Answer sont des valeurs stringifiées et les objets Arrays / JSON sont dynamiques.

Nous voulons convertir le tableau ci-dessus au format ci-dessous pour le rendre convivial BI / Visualization.

Table souhaitée:

 +-------------------------------------------------------------+
 | 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

Ci-dessous, pour 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
  """;     

si appliquer aux exemples de données de votre question

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"}' 
)    

la sortie est

Remarque: une EXECUTE IMMEDIATEpartie du script ci-dessus est exactement la même que dans l'article précédent - le changement concerne uniquement la préparation des données d'origine dans la table temporaire dataet leur utilisation dansEXECUTE IMMEDIATE