Generate JSON with property names that include dot using SELECT FOR JSON

All we need is an easy explanation of the problem, so here it is.

I need to generate the folowing JSON payload (shortened) from a table in SQL Server. Please note the dot in the property name. This is a special syntax called OData.

{
  "Id" : "A1",
  "value": {
    "[email protected]": "systemusers(key='AAAA12334')"
  }
}

Imagine the table is like the following:

I have tried the following T-SQL command:

SELECT [Id], [CreatedBy] AS [[email protected]]
FROM [Account]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Which obviously results to:

{
  "Id" : "A1",
  "value": {
    "[email protected]": {
      "bind": "systemusers(key='AAAA12334')"
    }
  }
}

I have already read the full documentation around JSON functionality in SQL Server thoroughly and no where in the documentation escaping dot in property names has been described.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use FOR JSON AUTO which ignores . characters in keys.

In this case, you need to place it in a subquery, otherwise you cannot get the value key

SELECT
  [Id],
  JSON_QUERY((
    SELECT
      [CreatedBy] AS [[email protected]]
    FROM (VALUES(1)) v(dummy)   -- because AUTO needs at least one table
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
  )) AS value
FROM [Account]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER```

db<>fiddle

Method 2

One solution (in addition to my other answer) is to be a bit more explicit in the JSON structure you wish to generate. Basically, here you are providing FOR JSON with some already properly-parsed JSON code.

SELECT
    [Id], 
    JSON_QUERY('{"[email protected]":"'+CreatedBy+'"}') as value
FROM (SELECT 'A1'  as ID, 'AAAA12334' as CreatedBy) AS account
FOR JSON PATH , without_array_wrapper

This solution retains the column name generated by FOR JSON.

Method 3

One solution is to create your own escape sequence, and then use procedural T-SQL with @variables, rather than pure relational query language.

Given the inability to directly escape characters in strings for the FOR JSON parser(*), here we create our own escape sequence which we later have to replace manually.

DECLARE @str as nvarchar(MAX) = (
    SELECT
        [Id], 
        [CreatedBy] AS [[email protected]%2Ebind]
    FROM (SELECT 'A1'  as ID, 'AAAA12334' as CreatedBy) AS account -- your dummy data
    FOR JSON PATH , without_array_wrapper)
set @str=REPLACE(@str,'%2E','.')
select @str as formatted_json

where %2E was used because it is the escape sequence for a . character in URIs

Obviously if %2E could conceivably be part of the value portion of the innermost key, you might have to devise a different substitution or use a more robust parser to only target the particular key that you wanted.

(*)I tried at first to see if FOR JSON would use \ , %20 and   to escape a space character within a string, but none of those worked.

Note that an attempt to try to use the self-escaped string in a subquery or cte — rather than using a procedural variable as an intermediary — failed. The column name generated by FOR JSON is a "random" GUID value (note: see screenshot provided for other answer) that I couldn’t figure out how to extract. T-SQL has no way of selecting the column by position, so doing something like SELECT COLUMN(1) FROM (...) does not work. You can’t even reference the column by embedding it in a subquery and wrapping with a SELECT * FROM (...) AS sq, as that produces the error: No column name was specified for column 1 of 'sq'.

Similarly UNIONs where you bind the "subquery" result to a dummy table with a defined column name didn’t seem to be a fruitful path either, as JSON parsing appears to operate after the union operation is complete. Parentheses didn’t appear to correct this in my limited testing.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0