Packing Columns as JSON in BigQuery

Properties2

Type	Practice
Note created	May 6, 2025

This snippet shows how to dynamically pack multiple columns into a single JSON string in BigQuery, while excluding specific columns you don’t want in the JSON output.

The key is to use a correlated subquery with a table alias and the TO_JSON function:

WITH
  source_cte AS (
    SELECT
      trip_id AS traj_id,
      * EXCEPT (properties, trip_id),
      ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY t) AS prop_a,
      ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY t) * 2 AS prop_b
    FROM `cartobq.docs.taxi_trips_porto_cast`
    LIMIT 200
  )
SELECT
  s.traj_id,
  TO_JSON((SELECT AS STRUCT s.* EXCEPT(traj_id, lat, lon, t))) AS properties
FROM source_cte s

The important parts of this pattern are:

1. Alias the main table reference (here using s)
2. In the subquery, select from the current row using s.* rather than from the entire table
3. Use EXCEPT to exclude the specific columns you don’t want in the JSON

The code is equivalent to using TO_JSON(prop_a, prop_b), but using EXCEPT is very useful when the additional columns are unknown when writing the query.