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

Question

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:

Id	CreatedBy
A1	AAAA12334

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": {
    "createdonbehalfby@odata": {
      "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.

Answer 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

Result
{"Id":"A1","value":{"[email protected]":"AAAA12334"}}

Answer 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.

Answer 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 [value.createdonbehalfby@odata%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.