OPENJSON() vs JSON_VALUE() When Parsing JSON in SQL Server

Working with JSON in SQL Server often comes down to choosing the right function for the job. Two of the most common options are OPENJSON() and JSON_VALUE(). Both are designed to pull data out of JSON documents, but they work in very different ways and are suited to different scenarios. Knowing when to use each one can save time and simplify your queries.

This article breaks down how OPENJSON() and JSON_VALUE() handle JSON parsing, what makes them different, and the situations where one might be a better fit than the other. Whether you are cleaning up nested JSON or just extracting a single value, understanding these functions will help you work more effectively with JSON data in SQL Server.

JSON_VALUE()

Think of the JSON_VALUE() function as a scalpel. It’s used for extracting a single scalar value from a JSON string. A scalar value is just a single number, string, or boolean. It’s not an object or an array. Therefore, the JSON_VALUE() function is a fast and efficient way of getting a specific piece of data.

Example

Let’s say you have a table storing JSON data about car parts:

CREATE TABLE dbo.Parts (
    PartID INT PRIMARY KEY,
    PartDetails NVARCHAR(MAX)
);

INSERT INTO dbo.Parts (PartID, PartDetails) VALUES
(1, N'{"name": "Spark Plug", "price": 12.99, "available": true, "partNumber": "SP-721"}'),
(2, N'{"name": "Brake Pad Set", "price": 45.50, "available": false, "partNumber": "BP-005"}'),
(3, N'{"name": "Oil Filter", "price": 8.75, "available": true, "partNumber": "OF-991"}');

To find the part number for the Spark Plug, you can use JSON_VALUE():

SELECT 
    PartID, 
    JSON_VALUE(PartDetails, '$.partNumber') AS PartNumber
FROM dbo.Parts
WHERE JSON_VALUE(PartDetails, '$.name') = 'Spark Plug';

Output:

PartID  PartNumber
------  ----------
1       SP-721    

Notice how the '$.name' and '$.partNumber' paths point directly to the values you want. The '$.' is for specifying the root of the JSON object.

Note: While SQL Server 2025 recently added a native JSON type, I’ve used NVARCHAR(MAX) here. This keeps the code backward compatible, so it’ll work whether you’re on the latest version or an older one.

OPENJSON()

If JSON_VALUE() is a scalpel, OPENJSON() is the carving knife. It’s used to parse a JSON object or array and transform it into a relational table with rows and columns. This is scenarios where you need to work with multiple key-value pairs or with nested JSON.

Example 1: Parsing a JSON Array

Let’s imagine you have a JSON string listing an engine’s components:

DECLARE @jsonArray NVARCHAR(MAX);
SET @jsonArray = N'[
    {"name": "Piston", "count": 4},
    {"name": "Crankshaft", "count": 1},
    {"name": "Camshaft", "count": 2}
]';

SELECT *
FROM OPENJSON(@jsonArray);

Output:

key  value                               type
---  ----------------------------------  ----
0    {"name": "Piston", "count": 4}      5   
1    {"name": "Crankshaft", "count": 1}  5   
2    {"name": "Camshaft", "count": 2}    5   

By default, OPENJSON() returns a table with key, value, and type columns. But this isn’t very useful yet. To make it a proper table, you need to use the WITH clause:

SELECT 
    component.name, 
    component.count
FROM OPENJSON(@jsonArray)
WITH (
    name NVARCHAR(100) '$.name',
    count INT '$.count'
) AS component;

Output:

name        count
----------  -----
Piston      4    
Crankshaft  1    
Camshaft    2    

Here, the WITH clause acts like a schema, defining the columns and their data types, and the paths ('$.name') tell OPENJSON() where to find the data.

Example 2: Parsing a Nested JSON Object

Now, let’s look at a more complex example with nested data. Suppose you have a single JSON object for a car model that includes a nested object for its engine:

DECLARE @jsonObject NVARCHAR(MAX);
SET @jsonObject = N'{
    "model": "Roadster",
    "manufacturer": "Bolt Motors",
    "year": 2024,
    "engine": {
        "type": "Electric",
        "hp": 350,
        "torque": 400
    }
}';

To extract the engine’s details, you can use OPENJSON() to navigate the nested structure:

SELECT 
    engine.*
FROM OPENJSON(@jsonObject, '$.engine')
WITH (
    engineType NVARCHAR(50) '$.type',
    horsepower INT '$.hp',
    torque INT '$.torque'
) AS engine;

Output:

engineType  horsepower  torque
----------  ----------  ------
Electric    350         400   

Here, the '$.engine' in OPENJSON(@jsonObject, '$.engine') tells the function to start parsing from the engine object, and the WITH clause then defines the columns for the values inside that object.

Summary

Here’s a summary of the difference between the two functions:

JSON_VALUE()	OPENJSON()
Finds one specific item.	Unpacks a whole container of items.
Use when you need a single value.	Use when you need to turn a JSON object or array into a table with multiple rows or columns.

So use JSON_VALUE() when you need to quickly access a specific piece of data. Use OPENJSON() when you need to shred a JSON array or object into a relational format, making it easy to query multiple values or join it with other tables.