JSON

Question 1

What’s the difference between FOR JSON AUTO and FOR JSON PATH?

Answer 1

The latter gives more control to the user to parse the JSON than the former.

1. When using FOR JSON AUTO, isn’t possible nest properties by using alias on the SELECT clause. Let’s suppose I want return the json below by using this query:

SELECT
‘Alfonso’ AS [key1.subKey1]
FROM my_table
FOR JSON_AUTO;

{
     key1: {
               subKey1: "Alfonso"
     }
}

The query above will produce the following json:

{
“key1.subKey1”: “Alfonso”
}

The way to achieve nested properties is by making joins (but it’s a bit ugly due the former table when making joins will be the parent property, so it’s not well controlled). However, by using FOR JSON PATH, it’s possible to achieve the previous format by aliasing columns.

Question 2

What does do the INCLUDE_NULL_VALUES subclase in FOR JSON [AUTO | PATH] clause?

Answer 2

As his name says: it includes null values in the JSON. Default’s configuration removes properties that are null.

Question 3

What does do the ROOT(string) subclause?

Answer 3

According to the string passed as argument, it defines as root property. For example: ROOT(‘Orders’), it’ll output:

{
    Orders: [
     ...
    ]
}

Question 4

What does do the WITHOUT_ARRAY_WRAPPER?

Answer 4

Instead of returning an array of objects (formatting the JSON with []), it returns each object seppared by comma. If you put this JSON in a formatter, it’ll say that there’s an error because if there’s many objects, they need to be wrapped in an array, not standalone.

Question 5

Is it possible use ROOT, INCLUDE_NULL_VALUES, and WITHOUT_ARRAY_WRAPPER subclases in FOR JSON AUTO?

Answer 5

Yes, it is possible. Those subclauses are valid either AUTO or PATH.

Question 6

What’s the difference between FOR JSON AUTO and FOR JSON PATH when using joins?

Answer 6

When doing FOR JSON AUTO, if the columns from the first table appears before than columns from second table, then columns from the first table will be parents properties, whereas columsn from the second table will be children. Also, the children will be wrapped into an array.

Check the json resulted by running the following query:

SELECT
	TOP (2) soh.SalesOrderID,
	sod.SalesOrderDetailID,
	soh.OrderDate,
	sod.ProductID
FROM Sales.SalesOrderHeader AS soh
	INNER JOIN Sales.SalesOrderDetail AS sod
		ON soh.SalesOrderID = sod.SalesOrderID
FOR JSON AUTO;

JSON:

[
    {
        "SalesOrderID": 43659,
        "OrderDate": "2011-05-31T00:00:00",
        "sod": [
            {
                "SalesOrderDetailID": 1,
                "ProductID": 776
            },
            {
                "SalesOrderDetailID": 2,
                "ProductID": 777
            }
        ]
    }
]

In case using FOR JSON PATH, each row will be an entire object.

Question 7

Does the order of the columns affect in a FOR JSON clause?

Answer 7

Yes, it does. The order of columns defined in the SELECT clause alters the order of how the properties will appear in the JSON. Also, in case the FOR JSON AUTO clause is been used, then the order will affect the parent-children behavior in the JSON in case using JOINS.

Question 8

What’s the difference between the function JSON_VALUE() and JSON_QUERY()?

Answer 8

The JSON_VALUE returns a field of the json file, such as a number, string, boolean. The JSON_QUERY function it’s used for return an object, or an array.

Question 9

During accesing to json properties, what’s the difference between “lax” and “strict”?

Answer 9

Both are json path mode. It’s used before accesing the json object with “$” sign. “lax” is the default path mode, and it returns NULL in case a path is invalid, or the field declared by the path isn’t over there. “strict” as his name says, it raises an error in case the path is invalid, or the field isn’t over there.

For example:

{
“Id”: “132132”
}

“lax $.Name” -> this will return NULL, cause Name field isn’t declared on the json (remember lax is the default mode, so it could be removed).
“strict $.Name” -> this will raise an error, and the operation will be stopped.

Question 10

What’s the syntax for use the OPENJSON function?

Answer 10

OPENJSON(json_string [, start_from_this_path]) [with_clause]

with_clause: = WITH ( {column_name data_type json_path}, [column_name NVARCHAR(MAX) json_path AS JSON] );