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"
}
]```
>Solution :
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
