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



  • 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": {
        "createdonbehalfby@odata.bind": "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 [value.createdonbehalfby@odata.bind]
    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.



  • 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 [createdonbehalfby@odata.bind]
        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```
    

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a9621600c1c61769bd49907e3fbe8c7

    Result
    {"Id":"A1","value":{"createdonbehalfby@odata.bind":"AAAA12334"}}



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2