JSON Path Column Ordering

I recently came across an error when using FOR JSON PATH with SQL Azure. The error message led me to think that I had a duplicate output column name in my SELECT statement but this wasn’t the case.

The Error

In a nutshell, I was running a SELECT ... FOR JSON PATH statement which returned the following error:

Property 'customer.country' cannot be generated in JSON output due to a conflict with another column name or alias. Use different names and aliases for each column in SELECT list.

Conflicting Column?

Seeing conflict with another column name or alias in the error made me immediately think there was a duplicate column name. This was a fairly large SQL statement so it wasn’t beyond the realms of possibility that I had a duplicate column name. However, after some troubleshooting I realised that this wasn’t the case. The issue was to do with the ordering of my column names - not their uniqueness. Specifically around the ordering of nested JSON elements. By using a dot (‘.’) in my column alias’ I can generate nested JSON properties. However, all nested properties need to be grouped together.

Example

Take the following example - each column alias is unique but the above exception is thrown when it is run.

SELECT
  '123' AS [customer.id],
  'mary' AS [customer.name],
  12.99 AS [order.price],
  'uk' AS [customer.country],
  CURRENT_TIMESTAMP AS [order.date]
FOR JSON PATH

Under the hood I’m assuming the SQL engine must do something like:

1. Create ‘customer’ object, add ‘id’ and ‘name’ properties ([customer.id] and [customer.name]).
2. Create ‘order’ object, add ‘price’ to it ([order.price])
3. For ‘customer.country’, create ‘customer’ object. This already exists so throw an exception.

Now that I realised how the JSON is generated, the fix was relatively simple - reorder the columns so that they are ‘grouped’ together. The above SQL becomes:

SELECT
  '123' AS [customer.id],
  'mary' AS [customer.name],
  'uk' AS [customer.country],
  12.99 AS [order.price],
  CURRENT_TIMESTAMP AS [order.date]
FOR JSON PATH

which successfully outputs:

[
  {
    "customer": {
      "id": "123",
      "name": "mary",
      "country": "uk"
    },
    "order": {
      "price": 12.99,
      "date": "2019-10-25T14:44:37.310"
    }
  }
]