How to remove the square brackets in child rows of a json from SQL Server 2016?

Question

I have a problem with the ouput for a query like this:

SELECT
    Users.Id,
    Users.FbId,
    Users.Email,
    Users.Nombre,
    (SELECT * FROM AccessLevel WHERE AccessLevel.Id = Users.NivelAcceso FOR JSON PATH) AS NivelAcceso,
    (SELECT * FROM UserStatus WHERE UserStatus.Id = Users.Estatus FOR JSON PATH) AS Estatus
FROM 
    Users 
WHERE 
    Users.Id = 1 
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

The resulting of that is like this:

{
  "Id": 1,
  "Email": "[email protected]",
  "NivelAcceso": [
    {
      "Id": 1,
      "Clave": "Usuario"
    }
  ],
  "Estatus": [
    {
      "Id": 1,
      "Estatus": "Activo"
    }
  ]
}

The problem is when i deserialize that in C# with Newtonsoft, the square brackets in "NivelAcceso" and "Estatus" can't be parse because Newtonsoft thinks is an Array.

My C# classes are this:

public class AccessLevel
{
    [JsonProperty(PropertyName = "Id")]
    public int Id { get; set; }
    [JsonProperty(PropertyName = "Clave")]
    public string Clave { get; set; }
    [JsonProperty(PropertyName = "Descripcion")]
    public string Descripcion { get; set; }
}

public class UserStatus
{
    [JsonProperty(PropertyName = "Id")]
    public int Id { get; set; }
    [JsonProperty(PropertyName = "Estatus")]
    public string Estatus { get; set; }
    [JsonProperty(PropertyName = "Descripcion")]
    public string Descripcion { get; set; }
}

public class Users
{
    public long Id { get; set; }
    public string Email { get; set; }
    [JsonProperty(PropertyName = "NivelAcceso")]
    public AccessLevel NivelAcceso { get; set; }
    [JsonProperty(PropertyName = "Estatus")]
    public UserStatus Estatus { get; set; }
}

Actually I'm using something like this:

Users data = JsonConvert.DeserializeObject<Users>(_dataReader[0].ToString().Replace('[', '\0').Replace(']', '\0'));

Anybody can help me how to deserialize that?

Answer 1

In your sub query you also need to specify WITHOUT_ARRAY_WRAPPER modifier

SELECT
    Users.Id,
   ...
    (SELECT * FROM AccessLevel WHERE AccessLevel.Id = Users.NivelAcceso FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS NivelAcceso,

But when you do this, the outer JSON will treat your "NivelAcceso" field as a plain string instead of a json object. To solve this use JSON_QUERY function

    SELECT
            Users.Id,
           ...
            JSON_QUERY((SELECT * FROM AccessLevel WHERE AccessLevel.Id =  Users.NivelAcceso FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS NivelAcceso,
     ...
     FROM Users 
     WHERE Users.Id = 1 
     FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;