On September 25, 2024, I learned ...

How to create a json object in a SQL query

It’s possible to create JSON objects from a table in SQL. For example, given a table with columns colA, colB, colC if I wanted to create a new table with columns id and json_object where the values in the json_object column were {'mycolA': value_from_colA, 'mycolB': value_from_colB} the appropriate SQL query would be


SELECT colC as id,
ARRAY_AGG(JSON_OBJECT('mycolA', colA, 'mycolB', colB)) AS json_object
FROM `my_project.my_table.this_cool_table`

This should create a new table that looks like this:

id	json_object
1	{‘mycolA’: ‘value1’, ‘mycolB’: ‘value2’}
2	{‘mycolA’: ‘value3’, ‘mycolB’: ‘value4’}