Convert MongoDB Extended JSON to BigQuery columns: the SQL for every BSON wrapper, in both Datastream formats

What does MongoDB data look like in BigQuery?

Usually like one JSON value per document. Google's Datastream writes MongoDB documents as Extended JSON, the Dataflow batch template's default puts the whole document into a STRING column called source_data, Airbyte's schemaless mode lands _id and data, and Fivetran's default packed mode keeps the document in a data column. The typed columns your reports need are something you build on top. The connectors that do infer columns do it from a sample, which is its own problem; we compared every option on our MongoDB to BigQuery connector comparison.

Extended JSON exists because JSON has no dates, no 64-bit integers and no decimals. MongoDB solves that by wrapping each such value in an object whose key starts with a dollar sign. That keeps the type, and it is why a date you expected to read as created_at arrives as a nested object two levels deep. In the examples below, doc stands for the column holding the document, whatever your connector named it.

How do BSON types map to BigQuery?

The table shows the two formats Datastream can write, taken from Google's own type mapping page, the extraction that works for each, and the BigQuery type to cast into. Dataflow's JSON and NONE modes and mongoexport's canonical output use the same wrappers.

The wrapper keys start with $, which JSONPath treats as special, so each one goes in double quotes inside the path. '$.createdAt."$date"' reads the key named $date under createdAt. JSON_VALUE accepts both a JSON column and a STRING holding JSON, so the same expressions work on Datastream output and on the Dataflow template's source_data string.

How do I convert a MongoDB date to a BigQuery timestamp?

Read the milliseconds as a string, cast them to INT64, and pass them to TIMESTAMP_MILLIS. BSON dates are milliseconds since the Unix epoch in UTC, so the result is an exact TIMESTAMP with no time zone guesswork. Because canonical and strict put the number at different paths, and mongoexport's relaxed format writes an ISO string instead, the safe expression tries all three in order:

COALESCE(
  SAFE.TIMESTAMP_MILLIS(SAFE_CAST(
    JSON_VALUE(doc, '$.createdAt."$date"."$numberLong"') AS INT64)),
  SAFE.TIMESTAMP_MILLIS(SAFE_CAST(JSON_VALUE(doc, '$.createdAt."$date"') AS INT64)),
  SAFE.TIMESTAMP(JSON_VALUE(doc, '$.createdAt."$date"'))
) AS created_at

On a canonical document the first line matches. On a strict one the first returns NULL, because the path does not exist, and the second matches. JSON_VALUE returns NULL for an object, which is what lets the chain fall through cleanly. Store the result as TIMESTAMP rather than DATETIME, or US reports will quietly shift by four or five hours depending on the season.

How do I keep Decimal128 and long values exact?

Extract the wrapper's string and cast it, never the JSON number. A Decimal128 amount arrives as {"$numberDecimal": "1234567890.1234567890"}. SAFE_CAST(JSON_VALUE(doc, '$.total."$numberDecimal"') AS NUMERIC) keeps it exact for money with up to nine decimal places, and BIGNUMERIC takes the rest. Longs behave the same way: a value above 2⁵³ survives as a string and does not survive a trip through FLOAT64. If an old app version wrote the same field as a plain number, add it as a last COALESCE branch rather than widening the column.

How do I turn MongoDB arrays into BigQuery rows?

UNNEST the array with its position, one row per element, and treat the result as a child table keyed on the parent id plus the offset. Line items on an order are the usual case, and they are where revenue reports go wrong if the array stays as a blob:

SELECT
  JSON_VALUE(doc, '$._id."$oid"') AS order_id,
  pos AS line_number,
  JSON_VALUE(item, '$.sku') AS sku,
  SAFE_CAST(COALESCE(JSON_VALUE(item, '$.qty."$numberInt"'),
                     JSON_VALUE(item, '$.qty')) AS INT64) AS qty,
  SAFE_CAST(JSON_VALUE(item, '$.price."$numberDecimal"') AS NUMERIC) AS unit_price
FROM raw_mongo.orders,
  UNNEST(JSON_QUERY_ARRAY(doc, '$.items')) AS item WITH OFFSET AS pos

Why are my MongoDB fields NULL in BigQuery?

Almost always because the path does not match the shape in that row. The four usual causes: the table mixes strict and canonical documents, the field is stored with a different type in older documents, the key is missing rather than null, or a sampling connector never created the column. None of these raise an error. This query tells you which shape each table holds, and how many rows hold neither:

SELECT
  COUNTIF(JSON_VALUE(doc, '$.createdAt."$date"."$numberLong"') IS NOT NULL)
    AS canonical_dates,
  COUNTIF(SAFE_CAST(JSON_VALUE(doc, '$.createdAt."$date"') AS INT64) IS NOT NULL) AS strict_dates,
  COUNTIF(SAFE.TIMESTAMP(JSON_VALUE(doc, '$.createdAt."$date"')) IS NOT NULL) AS iso_dates,
  COUNTIF(JSON_QUERY(doc, '$.createdAt') IS NULL) AS missing,
  COUNT(*) AS total_rows
FROM raw_mongo.orders

If the four counts do not add up to the total, some rows hold a date in a shape nobody planned for, often a string written by a script years ago. Run the same query against the collection count in MongoDB too. If you use strict mode, a gap there has a documented cause: Google states that Datastream discards documents containing NaN or Infinity in strict mode.

Should I use canonical or strict mode in Datastream?

Canonical, for almost every team. Google describes it as the format that "prioritizes data fidelity", it keeps 32-bit and 64-bit integers apart, and it never discards a document for holding NaN. Strict exists for compatibility with plain JSON parsers. Whichever you pick, set it explicitly on the stream rather than inheriting the default, because the default is what changed on 16 September 2026, and write views that read both shapes if you run older streams.

Is it cheaper to keep MongoDB data as JSON in BigQuery?

Cheaper to land, dearer to query. Storage is similar either way. The difference is in compute: BigQuery cannot partition or cluster on a JSON column, so a dashboard filtering on a status or a date buried inside the document reads far more data than one filtering on a typed, clustered column. Promote the ten or so fields your reports filter and join on, and leave the rest in JSON. If the warehouse bill is already under scrutiny, it helps to see cloud spend broken down by service and project before and after the change, so the saving is measured rather than assumed.

Can I make the typed columns without writing views?

Yes, by moving the typing into the pipeline instead of the warehouse. The views above work, but every new field is a pull request, and every app release that changes a type is a silent NULL until someone notices. A mapped sync declares the document paths once, casts them on the way in, lands arrays as child tables and keeps the raw document beside them. That is what Adapters does, and the demo at the top of this page shows a real MongoDB order mapped onto BigQuery columns. For the other sources feeding the same warehouse, see BigQuery ETL tools and how to load data into BigQuery. If some of your MongoDB data is heading to PostgreSQL instead, the same type-by-type approach is in converting MongoDB to PostgreSQL tables and JSONB.

How do I get the current state of each document from an append-only stream?

Keep the latest row per document id and drop deletes. Datastream's append-only mode adds a datastream_metadata record with SOURCE_TIMESTAMP, CHANGE_SEQUENCE_NUMBER and a CHANGE_TYPE of INSERT, UPDATE-INSERT, UPDATE-DELETE or DELETE. Rank rows within each id by timestamp and sequence number, keep the first, and filter out DELETE. Skip this and a document updated forty times is summed forty times. How change streams feed this in the first place is covered on change data capture tools.