BigQuery SQL Select that returns Key Value Pairs and not two seperate columns

0
votes

I am trying to merge 2 of my columns in my select statement on bigquery so that I have one column with key value pairs rather than having 2 columns (one with the key and one with the value). I have attempted to use array_agg() but whenever I do it causes an error in the first line of my statement saying: "An expression references column colName which is neither grouped nor aggregated"

Any suggestions as to how to do this or what may be causing my array_agg error would be greatly appreciated.

1
sqlstructgoogle-bigqueryarray-agg
Please share your SQL, example input and desired output :) - Graham Polley
Important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See meta.stackexchange.com/questions/5234/… for why it is important! When you get enough reputation on SO - vote up answers that are helpful. There are more ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! - Mikhail Berlyant

1 Answers

3
votes

There's limited information in your question, but I'll have a stab at it anyway. Here's some examples using arrays, string concatenation, and structs:

#standardSQL
WITH
  key_values AS (
  SELECT
    'key1' AS k,
    'value1' AS v
  UNION ALL
  SELECT
    'key2' AS k,
    'value2' AS v)
SELECT
  [k,
  v] AS kv_array,
  CONCAT(k,':',v) AS kv_concat,
  STRUCT(k,
    v) AS kv_struct
FROM
  key_values

enter image description here