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.

• mpag: I'm still trying to figure this one out. However, meanwhile you might find these helpful: learn.microsoft.com/en-us/sql/relational-databases/json/… and learn.microsoft.com/en-us/sql/relational-databases/json/… . FOR JSON PATH splits and FOR JSON AUTO leaves it more "lumped". What you want is a hybrid. I'm also having difficulties wrapping this as a common table expression in a larger query, as I get e.g. No column name was specified for column 1 of 'cte'
• Reza: I have read them all. Thanks for your comment. The hybrid approach was indeed the approach I took finally to make it work without making it overly complex.

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.MS SSMS screenshot

• Reza: The real statements that I'm working on are actually a lot bigger that the portion I posted above, but to be honest I ended up using a different approach that is not far from your idea. The main blocking is the fact that there's no way to escape dot in FOR JSON PATH.
• Reza: So at the end my workaround is to use nested SELECT statements. This kind of statement can easily accommodate several more columns and still remain readable/ maintainable. SELECT [Id], JSON_QUERY(( SELECT [CreatedBy] FROM [Account] AS [Value] WHERE [Value].[Id] = [Payload].[Id] FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)) AS [Value] FROM [Account] AS [Payload] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

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.

• Reza: I have been down this route as well. It's like a dead-end :)