JSON in SQL Server Oracle Snowflake and BigQuery Compared

PostgreSQL, MySQL and SQLite are the three databases most developers can start on a laptop. The four that this article covers are the ones that arrive with a job: SQL Server in the enterprise stack, Oracle in the finance system, Snowflake and BigQuery in the warehouse. They all store and query JSON, they all do it differently, and moving a query from one to the next is a translation exercise in every part of the syntax. This guide puts the four side by side for the jobs you actually do: storing a document, reading a value, unpacking an array into rows, building a document from rows, changing one key and indexing. For each job it gives the form each engine uses and the traps that come with it.

Some honesty first, because this site's rule is that code is run before it is published. These four engines are not runnable in the lab behind the other articles. SQL Server's Linux image targets amd64 and requires accepting a license at startup, Oracle's images carry a vendor license, and Snowflake and BigQuery are hosted services. So nothing below was executed by me. The statements are written from the vendors' documentation, kept to forms that are long established, and the traps are the ones those documents state. Treat the syntax as a map and run each statement on your own system before relying on it. The engines I could run have separate, tested articles: PostgreSQL JSONB, MySQL JSON, SQLite JSON functions, ClickHouse and DynamoDB documents.

The individual articles for the four engines in this group are OPENJSON in SQL Server, FOR JSON in SQL Server, JSON_TABLE in Oracle, VARIANT colon syntax in Snowflake, LATERAL FLATTEN, JSON_VALUE in BigQuery and unnesting JSON arrays in BigQuery.

How each engine stores JSON

SQL Server has, for most of its history, no JSON type. A JSON document is text in an nvarchar(max) (or varchar(max)) column, optionally guarded by a CHECK (ISJSON(doc) = 1). The functions (JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON, FOR JSON) arrived in SQL Server 2016 and operate on that text, parsing it on each call. Recent releases add a native json data type that stores a binary representation, and with it comes support for JSON indexes. Check your version: on 2016 to 2022, the text model is the one you have, with the same cost profile as SQLite's text JSON (JSON functions in SQLite measured it).

Oracle has the longest history of the four. Since 12c, JSON lives in VARCHAR2, CLOB or BLOB columns with an IS JSON check constraint, and the SQL/JSON functions (JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_EXISTS, JSON_OBJECT, JSON_ARRAYAGG) read it. From 21c there is a native JSON data type with a binary format (OSON). 23ai adds JSON Relational Duality, which presents relational tables as JSON documents and back. With the check constraint in place, Oracle also allows a dot notation, t.doc.customer.name, as a shorthand for JSON_VALUE.

Snowflake has VARIANT, a column type that holds semi-structured data: JSON objects, arrays and scalars. You load JSON into it with PARSE_JSON('...') or from a file. Snowflake stores VARIANT in an internal columnar form and, per its documentation, extracts frequently queried paths into separate columns when the data is regular, which is why queries on VARIANT can be fast without any index. There is a size limit on a single value, so check the current documentation for yours.

BigQuery has a native JSON type, which is a binary representation of JSON. Before it existed, people stored JSON in STRING columns and used JSON_EXTRACT and JSON_EXTRACT_SCALAR; those functions still work. With the type, you create values with PARSE_JSON(string), TO_JSON(...) or JSON_OBJECT(...), and read with JSON_VALUE, JSON_QUERY and the field access operators.

The summary:

Reading a scalar

All four give you a path language and a function, and they all return text by default unless you ask otherwise.

-- SQL Server
SELECT JSON_VALUE(doc, '$.customer.name') FROM orders;

-- Oracle
SELECT JSON_VALUE(doc, '$.customer.name') FROM orders;
SELECT JSON_VALUE(doc, '$.total' RETURNING NUMBER) FROM orders;

-- Snowflake
SELECT doc:customer.name::string FROM orders;
SELECT doc:total::number(10,2) FROM orders;

-- BigQuery
SELECT JSON_VALUE(doc, '$.customer.name') FROM orders;
SELECT INT64(doc.qty) FROM orders;

The shapes differ in useful ways. SQL Server and BigQuery JSON_VALUE return a string: SQL Server's is nvarchar(4000), so a longer value is not returned in lax mode. Oracle returns VARCHAR2(4000) by default and lets you declare the return type with RETURNING, so typing is part of the call. Snowflake uses a colon path (doc:customer.name) and the cast operator ::. The result of a path in Snowflake is still a VARIANT: it prints with quotes for strings until you cast with ::string. BigQuery has typed accessor functions, STRING(), INT64(), FLOAT64() and BOOL(), that take a JSON value and convert it, and "lax" variants such as LAX_INT64() that convert leniently.

The common thread: reading a value from JSON gives you a string or a variant, and you cast to the type you want. A comparison in the WHERE clause that skips the cast compares text with text (or a variant with a number), the same trap as in the other databases (querying JSONB with the arrow operators shows it in PostgreSQL).

Objects and arrays

To get a sub-document and keep it as JSON, each engine has a second function:

• SQL Server: JSON_QUERY(doc, '$.items'). JSON_VALUE on an object or array returns NULL in lax mode and raises an error in strict mode, and JSON_QUERY on a scalar returns NULL, so you must choose the function by the shape you expect.

• Oracle: JSON_QUERY(doc, '$.items'), with WITH WRAPPER options to wrap scalars or multiple matches in an array.

• Snowflake: the colon path returns the VARIANT, whatever its shape: doc:items.

• BigQuery: JSON_QUERY(doc, '$.items') returns JSON; JSON_VALUE returns NULL for an object or array.

Null, missing and the value that looks absent

In JSON, a key can be missing, present with the value null, or present with a value. SQL has one NULL. Each engine maps the three differently, and none does it the way you assume:

• SQL Server: in lax mode (the default) JSON_VALUE returns SQL NULL for a missing path and for a JSON null, and raises no error. To tell them apart you use OPENJSON with its type column (type 0 is null) or JSON_PATH_EXISTS on 2022 and later. In strict mode a missing path is an error.

• Oracle: JSON_VALUE returns SQL NULL for both by default (NULL ON ERROR, NULL ON EMPTY). JSON_EXISTS tests for presence.

• Snowflake: a JSON null is a VARIANT null, which is a value, distinct from SQL NULL. A missing path returns SQL NULL. doc:a IS NULL is false for a present JSON null; cast it (doc:a::string) and it becomes SQL NULL. IS_NULL_VALUE() tests for the VARIANT null.

• BigQuery: JSON_VALUE returns SQL NULL for a missing path and for a JSON null. JSON_QUERY returns the JSON null value for a present null and SQL NULL for a missing path.

The practical advice is the same everywhere: decide whether your documents omit absent keys or write nulls, enforce it where you can, and test the three cases explicitly in any query that treats "no value" specially.

Unpacking an array into rows

This is the most common job, and the one where the syntax diverges most.

-- SQL Server
SELECT o.id, i.sku, i.qty
FROM orders AS o
CROSS APPLY OPENJSON(o.doc, '$.items')
  WITH (sku nvarchar(20) '$.sku', qty int '$.qty') AS i;

-- Oracle
SELECT o.id, jt.sku, jt.qty
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]'
       COLUMNS (sku VARCHAR2(20) PATH '$.sku', qty NUMBER PATH '$.qty')) jt;

-- Snowflake
SELECT o.id, i.value:sku::string AS sku, i.value:qty::number AS qty
FROM orders o, LATERAL FLATTEN(input => o.doc:items) i;

-- BigQuery
SELECT o.id, JSON_VALUE(item, '$.sku') AS sku, INT64(item.qty) AS qty
FROM orders AS o, UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item;

What each does with the edge cases, per the documentation:

• Empty array or missing key: CROSS APPLY OPENJSON drops the row (use OUTER APPLY to keep it). Oracle's JSON_TABLE in a comma join also drops it; LEFT OUTER JOIN ... ON 1=1 keeps it. Snowflake's FLATTEN drops it unless you pass OUTER => TRUE. BigQuery's UNNEST in a comma join drops it, and LEFT JOIN UNNEST(...) ON TRUE keeps it. In all four, the inner join is the default and a parent with no children disappears.

• Typed columns: OPENJSON WITH and JSON_TABLE COLUMNS declare types at the unpack, which also makes them the place where a bad value fails or becomes NULL according to the engine's rules. In Snowflake and BigQuery the typing happens in the select list with casts.

• Nested arrays: Oracle's JSON_TABLE has NESTED PATH, SQL Server chains OPENJSON calls, Snowflake chains FLATTEN calls or uses RECURSIVE => TRUE, and BigQuery chains UNNEST.

The detailed articles are reading JSON in SQL Server with OPENJSON, Oracle JSON_TABLE, LATERAL FLATTEN in Snowflake and unnesting JSON arrays in BigQuery. The same operation in the databases I could test is JSON_TABLE in PostgreSQL, in MySQL and json_each in SQLite.

Building a document from rows

-- SQL Server
SELECT o.id, o.placed,
       (SELECT i.sku, i.qty FROM items i WHERE i.order_id = o.id FOR JSON PATH) AS items
FROM orders o FOR JSON PATH;

-- Oracle
SELECT JSON_OBJECT('id' VALUE o.id, 'items' VALUE
         (SELECT JSON_ARRAYAGG(JSON_OBJECT('sku' VALUE i.sku, 'qty' VALUE i.qty))
          FROM items i WHERE i.order_id = o.id))
FROM orders o;

-- Snowflake
SELECT OBJECT_CONSTRUCT('id', o.id, 'items',
         (SELECT ARRAY_AGG(OBJECT_CONSTRUCT('sku', i.sku, 'qty', i.qty)) FROM items i WHERE i.order_id = o.id))
FROM orders o;

-- BigQuery
SELECT TO_JSON(STRUCT(o.id AS id,
         ARRAY(SELECT AS STRUCT i.sku, i.qty FROM items i WHERE i.order_id = o.id) AS items))
FROM orders o;

The one trap I will name, because it affects SQL Server in a way that matches the SQLite subtype bug (building JSON results with json_group_array): when a JSON string comes out of a scalar function rather than a FOR JSON subquery, SQL Server treats it as text and escapes it when it is embedded in a parent FOR JSON result. The remedy in the documentation is to wrap it in JSON_QUERY(...). Oracle's constructors have a FORMAT JSON clause for the same purpose, and Snowflake and BigQuery work on typed values (VARIANT or JSON) and so avoid most of it. The details for SQL Server are in building JSON from query results with FOR JSON.

Changing a value

• SQL Server: JSON_MODIFY(doc, '$.a.b', 5) returns a new document. Setting a value to NULL in lax mode removes the key. To insert an object you wrap with JSON_QUERY again, and append mode adds to arrays.

• Oracle: JSON_TRANSFORM(doc, SET '$.a.b' = 5, REMOVE '$.c') applies several operations in one call. JSON_MERGEPATCH applies an RFC 7396 patch.

• Snowflake: VARIANT values are immutable in the sense that you build a new one: OBJECT_INSERT(obj, 'key', value, TRUE) (the last argument updates an existing key), OBJECT_DELETE, ARRAY_APPEND.

• BigQuery: JSON_SET(doc, '$.a.b', 5), JSON_REMOVE and JSON_ARRAY_APPEND return new values, and you assign the result back with UPDATE.

Every one of them rewrites the whole document in the row, and the warehouse engines are happier with insert-only workloads than with many small updates.

Indexing

The shared lesson is the one every database teaches: the fields you filter on all day should be real columns, and the document should hold the rest. In the two warehouses that means partition and cluster keys are columns, and VARIANT or JSON holds the payload.

Size, speed and limits

Each engine has per-value size limits (SQL Server's nvarchar(max) at 2 GB, Snowflake and BigQuery with limits on a single JSON or VARIANT value, Oracle with limits that depend on the storage choice). They change between releases, so read the current documentation rather than trusting a number in an article. The behavior I would plan around in all four is that a large document is read whole for every query that touches it, and that the columnar warehouses do best when each JSON value is small and regular.

Getting JSON in

Loading is where most projects meet JSON first, and each engine has a native route.

SQL Server reads a file from disk or blob storage with OPENROWSET(BULK '...', SINGLE_CLOB), which returns the file's contents as one value you can pass to OPENJSON, as in SELECT * FROM OPENROWSET(BULK 'orders.json', SINGLE_CLOB) AS j CROSS APPLY OPENJSON(j.BulkColumn). That works for a single document or an array in one file and needs the file to be visible to the server, which is a permissions conversation in many shops. For many small documents you insert text from the application.

Oracle reads files through external tables, SQL*Loader or by inserting CLOB/BLOB values from the application. An IS JSON constraint on the target column validates on the way in, and a bad document raises an error for that row.

Snowflake loads files from a stage with COPY INTO my_table FROM @stage FILE_FORMAT = (TYPE = JSON). A JSON file format can strip an outer array (STRIP_OUTER_ARRAY = TRUE) so each element becomes a row, and the target table has a single VARIANT column, which is the standard landing pattern. A parse error stops the load or skips the row, depending on the ON_ERROR option.

BigQuery loads newline-delimited JSON with bq load --source_format=NEWLINE_DELIMITED_JSON dataset.table file.json schema, or with autodetection. A JSON array in one file (a single [...]) is not the supported shape for a load job; convert it to JSON Lines first, as in converting a JSON array to JSON Lines and back. Where a column is typed JSON, each value is a whole document and the load does not unpack it.

The consistent rule: the warehouses want one document per line, and the transactional engines want the application to hand them a document at a time. In both cases the cheapest thing to do is to land the raw JSON untouched, validate it in the database, then transform. A load that parses and reshapes in the same step turns every format surprise into a failed load, and a landed copy turns it into a query you can run again.

The error messages you will meet

Parsing errors are where the engines are most different in friendliness. From the vendor documentation, the ones that come up most:

• SQL Server reports JSON text is not properly formatted with the unexpected character and its position (error 13609) for malformed input, and Property cannot be found on the specified JSON path (13608) when a strict-mode path does not exist. In lax mode, the second case returns NULL instead.

• Oracle raises ORA-40441: JSON syntax error for malformed input, ORA-40462: JSON_VALUE evaluated to no value when you asked for an error on an empty result, and ORA-40478: output value too large when a JSON_VALUE result exceeds its declared size, which is the 4,000-byte default. The last one surprises people whose test data is short.

• Snowflake reports an Error parsing JSON message with the reason and a position for PARSE_JSON failures. TRY_PARSE_JSON returns NULL instead, which is the right function for a column of dubious text.

• BigQuery rejects invalid input to PARSE_JSON with an invalid-JSON error; SAFE.PARSE_JSON returns NULL. The SAFE. prefix works with most functions and turns an error into NULL.

If you take one practice from this list: use the "try" form (TRY_PARSE_JSON, SAFE.PARSE_JSON, ISJSON, IS JSON) at the boundary, count how many values fail, and decide what to do with them, before the transformation sees them. The same discipline applies to the open-source engines: fixing invalid JSON text errors in MySQL and finding the exact line of a JSON syntax error are the equivalent for those.

Case, quoting and path syntax

JSON keys are case-sensitive and SQL identifiers often are not, and that mismatch causes errors that look like missing data. A path such as $.Customer.Name will not match customer.name in any of the four; in Snowflake the colon path doc:Customer is case-sensitive too, and a key with unusual characters needs bracket syntax (doc['order-id']). SQL Server and Oracle both require double quotes around keys with spaces or special characters inside the path ($."order id"), and BigQuery uses the same rule with a subscript alternative.

The path languages are close relatives of one another and of JSONPath, but not identical. SQL Server's lax and strict prefixes, Oracle's filter expressions, Snowflake's colon and bracket notations and BigQuery's field access operator are all dialects. A path copied from one engine's example will often work on another for simple cases and fail on filters, wildcards and array slices. Treat the first query as a test: select a known value from a known row and check it before building on it.

Numbers, dates and big integers

JSON has numbers and no dates, and every engine has to decide how to read them.

Numbers. Each engine converts a JSON number to a typed value at the cast. Integers above 2^53 are the trap, as in every database that stores JSON; a JavaScript client reading the result will round them regardless of what the database did. When identifiers can exceed 2^53, store and transport them as strings (how JSON handles big numbers). Cast decimals deliberately: a money amount read as a floating-point number is a rounding bug waiting for a total.

Dates. The documents carry ISO 8601 strings, and the casts to a date or timestamp are where the formats are enforced. CAST(JSON_VALUE(...) AS datetime2) in SQL Server, TO_TIMESTAMP or RETURNING TIMESTAMP in Oracle, ::timestamp_ntz in Snowflake and TIMESTAMP(JSON_VALUE(...)) in BigQuery all depend on the string being in a format the engine accepts. A time zone offset in the string is a decision point: check whether your cast keeps it or silently converts. JSON dates lack a date type goes through the format choices.

Booleans. Snowflake and BigQuery keep them as booleans; SQL Server returns the text true or false from JSON_VALUE, and OPENJSON with an int or bit column maps them to 1 and 0; Oracle's JSON_VALUE returns 'true' or 'false' as text by default and accepts RETURNING NUMBER or a boolean return in newer releases.

Translating a query between engines

A rough crosswalk for the jobs above, including the engines I could test:

Where a name is the same across engines, the behavior at the edges usually is not: PostgreSQL's jsonb_set leaves the document unchanged for a missing parent, SQLite's json_set creates it, and SQL Server's JSON_MODIFY removes a key if you set it to NULL. When you port a query, port the tests for the edge cases with it: empty array, missing key, JSON null, wrong type, and a value too large for the return type.

Performance patterns

Four habits carry across all of them.

1. Extract once. Pull the fields a query needs into typed columns in a staging step (a computed column, a CTAS, a view that is materialized) instead of re-parsing the document in every predicate. In the engines I could measure, the repeated-parse cost was a factor of 3 to 10 against typed columns (MySQL JSON, SQLite and PostgreSQL all show it).

2. Filter on real columns first. Partition and cluster keys, indexes and date columns are what let an engine skip data. A predicate inside a document does not help it skip.

3. Keep documents small and regular. The columnar engines extract and compress repeated paths well when documents share a shape, and poorly when every row has different keys.

4. Do not update documents in place at scale. Update statements rewrite the whole document, and warehouses prefer appends and rebuilds.

Testing what you cannot run here

If you need to check a statement from this guide, each vendor has a free way in. SQL Server has a Developer edition and Oracle has a free edition of its database; both can be installed on a workstation, and in both cases the installer asks you to accept the vendor's license terms, which is a decision for you or your employer to make and the reason I did not run them for this article. Snowflake offers a time-limited trial account and BigQuery has a free sandbox with no billing account, both enough to run the Snowflake and BigQuery examples here against a few rows. Create a three-row table with an empty array, a missing key, a JSON null and a wrong-typed value, and run each statement against it before you point it at real data. Those four rows find nearly every difference described above.

A decision guide

• On SQL Server with a version before the native type, JSON is text. Keep it for payloads you store and return, put the fields you query in computed columns, and use OPENJSON for unpacking.

• On Oracle, use IS JSON or the native type, JSON_TABLE for rows, and think about duality views if the same data must be both relational and documents.

• On Snowflake, load raw JSON into VARIANT and parse it into typed columns in a later step; treat VARIANT as the landing zone and the typed table as the model.

• On BigQuery, prefer the JSON type to strings, load newline-delimited files, and extract once into typed columns when queries repeat.

For an overview of the same questions in the open-source engines, start with the PostgreSQL guide, and for the broader question of whether JSON in a database is the right design, choosing between a JSON column and a document database. A document can be formatted and checked in the browser with the JSON formatter before you load it anywhere.

When none of these is the right place for JSON

The data is relational with a few optional fields. Columns, with a JSON column for the optional part. In every engine above, a typed column is smaller, faster and constrained.

You need many small updates to documents. Warehouses (Snowflake, BigQuery) are built for loads and scans. Frequent single-document updates are expensive and are better served by an operational store.

You are using JSON only to ship data between systems. Keep it as a file (NDJSON or Parquet) and load it into typed tables. The warehouses read files directly from object storage.

You are tempted to rely on syntax you have not run. Everything here, from the null rules to the edge cases of array unpacking, should be verified on your engine and version before it is part of a production query. The vendors' documentation is the reference: SQL Server JSON, Oracle JSON Developer's Guide, Snowflake semi-structured data and BigQuery JSON functions.