Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Convert BigQuery rows to array of JSON

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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

enter image description here

Another option (with same output) is

select to_json_string(array_agg(t))
from your_table t
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading