Convert Array of an Array to Multiple Columns BigQuery SQL

Question

I am trying to convert an array of array to multiple columns. The data structure is as follows:

Column name: Inputs
{
  answers: [{
    type: END_TIME,
    answer:   [{
      int_value: 1015
    }]
  },{
    type: LOCATION,
    answer:   [{
      string_value: "SAN_JOSE"
    },{
      string_value: "CA"
    }]
  }],
  username: "xxxxx",
  status: COMPLETE
}

Ideal output would be

end_time location       username  status
1015     SAN_JOSE, CA   xxxxx     COMPLETE

But I am getting the following result:

end_time                 location                        username   status
[{                       [{                              xxxxx      COMPLETE
      int_value: 1015        string_value: "SAN_JOSE"
    }]                   },{
                             string_value: "CA"
                         }]

Using the sample statement below

SELECT
  (SELECT answer FROM UNNEST(Inputs.answers) where type = 'END_TIME') end_time,
  (SELECT answer FROM UNNEST(Inputs.answers) where type = 'LOCATION') location,
  t.Inputs.username username,
  t.Inputs.status status
FROM table_name t
;

Any suggestions would be greatly appreciated. TIA!

kiran mathew · Accepted Answer · 2022-12-21 12:51:11Z

1

Can you try this query:

with table_name as(
select struct([struct("END_TIME" as type,["1015"] as answer),struct("LOCATION" as type,["SAN_JOSE","CA"] as answer)]as answers,
            "xxxxx"as username,
            "COMPLETE"as status)inputs
)
SELECT
 t.inputs.username,
 t.inputs.status
 ,answers.type
 ,array_to_string(answers.answer, ",")answer
 FROM table_name t , unnest(t.inputs.answers)answers
 
pivot(
 max(answer) for type in ("END_TIME","LOCATION")
)

answered Dec 21, 2022 at 12:51

kiran mathew

2,3931 gold badge7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ddoctor Over a year ago

Thank you for your suggestion. However, the table that you have created is different from the structure I have provided. I have tried array_to_string but I am getting an error: SQL_ANALYSIS_ERROR: No matching signature for function ARRAY_TO_STRING

ddoctor Over a year ago

I just found out that the data is not an array of strings but an array of protos.

kiran mathew Over a year ago

As per my understanding the data structure you have provided will create a table something like this, from the data structure is hard to recreate as its not in valid JSON format. If your table looks any different then please let me know. Also you can try this link to rectify the error

Collectives™ on Stack Overflow

Convert Array of an Array to Multiple Columns BigQuery SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related