Convert BigQuery rows to array of JSON-CodePudding

I wan to convert all the rows of BigQuery query output to an array of JSON. For example: I want to convert the following output rows

Col1	Col2
ex1a	ex1b
ex2a	ex2b

Convert this to the following JSON:

           { 
             "Col1":"ex1a",
             "Col2":"ex1b"
            },
            {
             "Col1":"ex2a",
             "Col2":"ex2b"
            }
    ]```

CodePudding user response：

Use below approach

select format('[%s]', string_agg(to_json_string(t)))
from your_table t

if applied to sample data in your question - output is

Another option (with same output) is

select to_json_string(array_agg(t))
from your_table t