Querying JSON Files with OPENJSON

OPENROWSET recognizes exactly three formats: CSV, PARQUET, and DELTA. JSON isn't one of them — there is no built-in JSON reader in serverless SQL.

The practical consequence is that reading JSON becomes a two-step problem instead of one. First, get the raw text of each JSON document into SQL as a plain string, untouched. Second, interpret that string as JSON using dedicated functions. OPENROWSET only ever does the first step here — it has no idea the text it's reading happens to be JSON.

The trick is to set every CSV structural character — field delimiter, field quote, row terminator — to something that will never naturally appear inside a JSON document. Nothing then splits, and each line comes through exactly as written.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'raw/payment_type.json',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '1.0',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0a'
)
WITH (
    jsonDoc NVARCHAR(MAX)
) AS [pt];

The result is a single column of raw, intact JSON strings:

Nothing has been parsed yet. This is still just text — the next two sections cover pulling actual structure out of it.

Worth a short detour, since 0x0B looks arbitrary until you know where it comes from.

Vertical Tab dates back to teletypes and line printers, where it told the machine to advance the paper down to the next preset row — the vertical counterpart to horizontal tab moving across to a column stop. Once printing moved away from that kind of hardware, the character lost its purpose. It's still valid in every text encoding, but almost nothing produces it intentionally anymore.

That's precisely why it works so well here. The goal was never to use vertical tab for its original meaning — it's simply obscure enough that no real JSON content will ever contain it by accident. Any similarly rare control character (0x1E, 0x1F) would serve the same purpose; 0x0B is just the conventional choice for this pattern.

Once a document is sitting in a column as text, JSON_VALUE extracts a single field from it by path.

SELECT
    JSON_VALUE(jsonDoc, '$.payment_type')  AS payment_type,
    JSON_VALUE(jsonDoc, '$.description')   AS description
FROM OPENROWSET(
    BULK 'raw/payment_type.json',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '1.0',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0a'
)
WITH (
    jsonDoc NVARCHAR(MAX)
) AS [pt];

$.payment_type means "the payment_type key at the document's root." The rules governing what comes back are simple and worth knowing exactly:

JSON_VALUE only ever returns one value. When a document is really an array — or you want several fields extracted at once, shaped as real columns — OPENJSON is the tool that expands JSON into a proper table.

SELECT *
FROM OPENROWSET(
    BULK 'raw/payment_type.json',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '1.0',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0a'
)
WITH (
    jsonDoc NVARCHAR(MAX)
) AS pt
CROSS APPLY OPENJSON(jsonDoc)
WITH (
    payment_type      SMALLINT,
    payment_type_desc VARCHAR(50)
) AS j;

CROSS APPLY runs OPENJSON once per incoming row, feeding it that row's jsonDoc value and joining the expanded result back to it — the standard pattern for turning one JSON column into several real columns.

When the shape isn't known ahead of time

A WITH clause assumes a known, fixed set of fields. When the document's structure is unpredictable, dropping WITH entirely gives back every key/value pair as generic rows instead:

SELECT *
FROM OPENROWSET(
    BULK 'raw/payment_type.json',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '1.0',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0a'
)
WITH (
    jsonDoc NVARCHAR(MAX)
) AS pt
CROSS APPLY OPENJSON(jsonDoc) AS kv;

The type column tells you what kind of value it is, which matters when the shape is genuinely unknown ahead of time:

A WITH clause normally extracts a plain scalar per column. When a field is itself an object or array, that default extraction quietly fails.

CROSS APPLY OPENJSON(jsonDoc)
WITH (
    payment_type SMALLINT,
    aliases      NVARCHAR(MAX)   -- always NULL: this path is an array
) AS j;

CROSS APPLY OPENJSON(jsonDoc)
WITH (
    payment_type SMALLINT,
    aliases      NVARCHAR(MAX) '$.aliases' AS JSON
) AS j;

AS JSON tells OPENJSON: don't try to extract this as a scalar, just hand back whatever's at this path as its own raw JSON text. That text can then be parsed further with a second, nested OPENJSON call if the individual array elements are needed as rows too.

One shortcut worth knowing: the '$.path' portion can be omitted whenever the JSON key name matches the column name exactly — OPENJSON assumes $.column_name by default. It's written out explicitly above only for clarity.

As a rule of thumb: reach for JSON_VALUE when a document is already one flat record and only a couple of fields are needed inline. Reach for OPENJSON the moment more than a couple of fields are needed as real typed columns, or the document actually contains an array that should become multiple rows.

One pass, combining the disguise trick with a nested field, using the same external data source pointer set up in Chapter 7.

-- Chapter 7's pointer, assumed already created:
-- CREATE EXTERNAL DATA SOURCE nyc_taxi_data WITH (LOCATION = '...')

SELECT *
FROM OPENROWSET(
    BULK 'raw/payment_type_array.json',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '1.0',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0a'
)
WITH (
    jsonDoc NVARCHAR(MAX)
) AS pt
CROSS APPLY OPENJSON(jsonDoc)
WITH (
    payment_type      SMALLINT,
    payment_type_desc VARCHAR(50),
    aliases           NVARCHAR(MAX) '$.aliases' AS JSON
) AS j;

Three ideas from this chapter land in a single query here: the delimiter disguise gets the raw text in, the mandatory alias makes the expansion legal, and AS JSON keeps the nested aliases array from silently disappearing.