If you’re trying to extract values from a JSON document, but one or more of the values on the same level have duplicate keys, you might run into problems if you try to extract those values using JSON_QUERY() or JSON_VALUE().

Both of those functions will only return the first value that matches the path. 

Fortunately, there’s another option.

The OPENJSON() function will return all values from any duplicate keys on the same level.

Example

Here’s an example to demonstrate OPENJSON() returning duplicate properties on the same level.

DECLARE @json NVARCHAR(4000) = N'{
    "dog" : {
            "details" : {
                "name" : "Fetch", 
                "name" : "Good Dog",
                "sex" : "male"
            }
        }
    }';
SELECT * FROM OPENJSON(@json, '$.dog.details');

Result:

+-------+----------+--------+
| key   | value    | type   |
|-------+----------+--------|
| name  | Fetch    | 1      |
| name  | Good Dog | 1      |
| sex   | male     | 1      |
+-------+----------+--------+

This example returns all children of the $.dog.details key.

In this case, we have two keys with the same name on the same level (the name key).

If we wanted to return just the values from the two name keys, we could do something like the following.

DECLARE @json NVARCHAR(4000) = N'{
    "dog" : {
            "details" : {
                "name" : "Fetch", 
                "name" : "Good Dog",
                "sex" : "male"
            }
        }
    }';
SELECT value FROM OPENJSON(@json, '$.dog.details')
WHERE [key] = 'name';

Result:

+----------+
| value    |
|----------|
| Fetch    |
| Good Dog |
+----------+

JSON_VALUE() & JSON_QUERY()

As mentioned, both JSON_VALUE() and JSON_QUERY() only return the first value that matches the path. 

So if we try to use those against the above JSON document, we get the following results.

JSON_VALUE()

JSON_VALUE() returns a scalar value from a JSON string, so it will return the following result.

DECLARE @json NVARCHAR(4000) = N'{
    "dog" : {
            "details" : {
                "name" : "Fetch", 
                "name" : "Good Dog",
                "sex" : "male"
            }
        }
    }';
SELECT JSON_VALUE(@json, '$.dog.details.name') AS [JSON_VALUE];

Result:

+--------------+
| JSON_VALUE   |
|--------------|
| Fetch        |
+--------------+

JSON_QUERY()

JSON_QUERY() extracts an object or an array from a JSON string, so it will return the following result.

DECLARE @json NVARCHAR(4000) = N'{
    "dog" : {
            "details" : {
                "name" : "Fetch", 
                "name" : "Good Dog",
                "sex" : "male"
            }
        }
    }';
SELECT JSON_QUERY(@json, '$.dog.details') AS [JSON_QUERY];

Result:

+--------------+
| JSON_QUERY   |
|--------------|
| {
                "name" : "Fetch", 
                "name" : "Good Dog",
                "sex" : "male"
            }              |
+--------------+

At least with JSON_QUERY() we get to see the duplicate keys, but we don’t get their individual values like we get with OPENJSON().