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

Question

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.

Please share your SQL, example input and desired output :) – Graham Polley Jun 25 '18 at 08:24 — Graham Polley, Jun 25 '18 at 08:24

score 4 · Answer 1 · answered Jun 25 '18 at 08:33

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

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

1 Answers1