In BigQuery, when should a column use the native JSON type instead of a STRING holding JSON?
answer
- one option is an opaque blob to the engine
- parsed at write time versus parsed on every read
- field access returns JSON, not a SQL type
- two extraction functions differ by the quotes
- stable schemas deserve real columns
basics
~20 sUse the native JSON type when the payload's shape varies but you query paths inside it: values are stored parsed, so path access is direct and only the referenced paths need reading. Keep STRING only for opaque payloads you never query into.
solid answer
~40 sA `STRING` column holding JSON is an opaque blob to BigQuery: every query that looks inside it reads the entire string and parses it at runtime with functions like `JSON_VALUE`. The native `JSON` type stores a parsed representation instead, so you navigate it with member and subscript syntax — `data.user.id`, `data["user"]["id"]` — and extract typed scalars with `INT64(...)`, `STRING(...)`, `BOOL(...)`, `FLOAT64(...)`. Because the value is structured rather than text, accessing one path does not require materialising the whole document. Choose `JSON` for semi-structured payloads whose schema drifts or varies per event. Choose `STRING` when the document is genuinely opaque — archived verbatim, forwarded elsewhere, never filtered on. And choose neither when the schema is actually stable: typed `STRUCT`/`ARRAY` columns compress better, prune better, and fail loudly on bad data.
go deeper
Know that BigQuery has a real JSON column type distinct from a STRING of JSON, and that you read paths out of it with field access or the JSON_VALUE function.
Explain what changes when the value is stored parsed rather than as text, and state precisely how JSON_VALUE and JSON_QUERY differ on a scalar path.
Show the judgment call across typed columns, JSON, and STRING for a real payload, including which keys you promote to real columns and how you cope with a producer renaming a key.
Own the contract with the producing teams: what is guaranteed in the schema, what may drift inside the document, and how a breaking change is detected before it silently NULLs a dashboard.
## Three ways to store semi-structured data BigQuery gives you a spectrum, and the interview question is really about picking the right point on it. **Typed columns (`STRUCT`, `ARRAY`, scalars).** The schema is declared. Each leaf is its own physical column, so compression is best, column pruning is exact, and a producer that sends the wrong type fails at load time. This is the right answer whenever the shape is known and stable, and candidates who jump straight to JSON without mentioning it have missed the main point. **The native `JSON` type.** The column holds a JSON *value*, stored in a parsed form rather than as text. You navigate it with field access and subscripts, and you convert leaves to SQL types explicitly. Right for payloads whose keys genuinely vary — per-tenant custom attributes, evolving event properties, third-party webhooks. **`STRING`.** The document is text. BigQuery knows nothing about its structure and cannot do anything with it except hand it to a function. Right only when the payload is opaque: kept for audit, replayed downstream, or parsed by something other than SQL. ## Working with the JSON type Build a JSON value with `PARSE_JSON('{"user":{"id":7}}')`, or convert a SQL value into one with `TO_JSON(some_struct)`. Navigate it directly: ```sql SELECT data.user.id, -- returns JSON INT64(data.user.id), -- returns INT64 STRING(data.user.name) -- returns STRING FROM events; ``` Field access returns another JSON value, which is why the conversion functions matter: comparisons and arithmetic need real SQL types. Quoted-key syntax `data["user-id"]` handles keys that are not valid identifiers, and integer subscripts index JSON arrays. The older extraction functions work on both a `JSON` value and a `STRING` of JSON, and their difference is the classic quiz item: - `JSON_VALUE(doc, '$.a')` returns a SQL `STRING` scalar with the JSON quoting removed, and returns NULL if the path points at an object or array. - `JSON_QUERY(doc, '$.a')` returns the JSON-formatted value — a string leaf comes back still quoted, and it can return whole objects and arrays. `JSON_VALUE_ARRAY` and `JSON_QUERY_ARRAY` are the array-returning counterparts, and `TO_JSON_STRING` serialises any SQL value back to text. Mixing up `JSON_VALUE` and `JSON_QUERY` is the single most common bug here: a filter written as `JSON_QUERY(doc, '$.status') = 'active'` never matches, because the left side is `"active"` with quotes. ## Why STRING gets expensive With a `STRING` column, every query that inspects a single key still reads the entire serialised document for every row it touches, then parses it. A wide payload with fifty keys costs the same whether you wanted one key or all fifty, and the parse work happens on every row of every query, forever. The native `JSON` type removes both halves of that: the value is already parsed at write time, and access is by path rather than by scanning text. That is also the argument for going one step further. If ninety percent of your queries touch three keys, promoting those three to typed top-level columns — either at load time or in a derived table — turns them into ordinary columns that partition, cluster, and compress like any other. Keeping the raw document alongside them as `JSON` for the long tail is a common and defensible hybrid. ## Caveats to raise unprompted - The stored value is a parsed representation, not the original text. Do not depend on byte-for-byte round-tripping of whitespace or key ordering; if the exact bytes matter for audit or signature verification, keep the original in a `STRING` column too. - Extremely large numeric literals need care when parsing, because JSON numbers and SQL numeric types do not have identical ranges; `PARSE_JSON` exposes a mode for how to handle numbers that do not fit. - Typed columns still beat JSON on compression and on scan cost, because a leaf column of one type encodes far better than a self-describing value. - Schema drift is not free just because the column tolerates it. Downstream queries hard-code paths, so a producer renaming a key silently returns NULL rather than failing — which is exactly the failure mode typed columns would have caught at load time. ## The answer an interviewer wants Structured and stable, use `STRUCT`/`ARRAY`. Semi-structured and queried, use `JSON`. Opaque and never queried, use `STRING`. Then say which keys you would promote to real columns and why.
- What is the difference between JSON_VALUE and JSON_QUERY on the path $.status of {"status":"active"}?`JSON_VALUE` returns the SQL string `active` with the JSON quoting stripped, which is what you want for comparisons and joins. `JSON_QUERY` returns the JSON-formatted value, so you get `"active"` including the quotes, and it can also return whole objects and arrays where `JSON_VALUE` returns NULL. A filter written with `JSON_QUERY` against a bare literal therefore never matches.
- You have a JSON column and one key is filtered in nearly every query. What do you do?Promote it to a typed top-level column — extracted at load time or in a derived table. As a real column it participates in clustering and partitioning, compresses properly, and is checked at write time. Keep the raw document in the JSON column for the long tail of rarely-used keys. That hybrid is the standard shape for evolving event data.
- Does the JSON type give you the same type safety as a STRUCT?No. A STRUCT declares each field's type, so a producer sending a string where an integer is expected fails at load. A JSON column accepts any well-formed document, so the same mistake lands silently and surfaces later as a NULL or a conversion error inside a query. JSON buys flexibility by moving validation from write time to read time.
saying these in an interview costs you the question
- Says JSON and STRING columns behave the same because both hold text
- Uses JSON_QUERY for scalar comparisons and wonders why nothing matches
- Reaches for JSON when the payload schema is fixed and known
- Assumes the JSON type preserves the original document byte for byte
- Thinks a JSON column can be used as the table's partitioning column