如何从SQL Server 2016中删除json的子行中的方括号? [英] How to remove the square brackets in child rows of a json from SQL Server 2016?

问题描述

对于这样的查询，我的输出有问题:

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;

结果如下:

{
  "Id": 1,
  "Email": "[e-mail]",
  "NivelAcceso": [
    {
      "Id": 1,
      "Clave": "Usuario"
    }
  ],
  "Estatus": [
    {
      "Id": 1,
      "Estatus": "Activo"
    }
  ]
}

问题是，当我使用Newtonsoft在C#中反序列化该字符串时，由于Newtonsoft认为是数组，所以无法解析"NivelAcceso"和"Estatus"中的方括号.

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.

我的C#类是这样:

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?

推荐答案

在子查询中，您还需要指定 WITHOUT_ARRAY_WRAPPER 修饰符

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,

但是，当您执行此操作时，外部JSON将把您的"NivelAcceso"字段视为纯字符串而不是JSON对象.要解决此问题，请使用

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;

这篇关于如何从SQL Server 2016中删除json的子行中的方括号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！