主页 / dba / 问题 / 296307
Accepted
HeyJude
Asked: 2021-07-29 11:26:36 +0800 CST

当join不相关时，基于同一张表上的多个查询创建一个json

  • 772

我需要根据同一张表上两个不同查询的结果构造一个 json，其中连接是无关紧要的。

考虑以下示例（此处为完整演示）：

CREATE TABLE Trees
(
    [Id] INT,
    [Type] NVARCHAR(100),
    [Height] DECIMAL(2,1)
);

INSERT INTO Trees ([Id], [Type], [Height])
VALUES 
 (1, 'Palm', 5.5), 
 (2, 'Pine', 6.2), 
 (3, 'Apple', 2.5), 
 (4, 'Japanese Cedar', 0.5), 
 (5, 'Spanish Fir', 0.6);

我想构建以下json：

{
  "highTrees": 
  [
     {
        "id": 1,
        "type": "Palm",
        "height": 5.5
     },
     {
       "id": 1,
        "type": "Pine",
        "height": 6.2
     }
  ],
  "lowTrees": 
  [
     {
        "id": 4,
        "type": "Japanese Cedar",
        "height": 0.5
     },
     {
       "id": 5,
        "type": "Spanish Fir",
        "height": 0.6
     }
  ]
}

我试过这个：

SELECT 
   Id as 'highTrees.id',
   Type as 'highTrees.type',
   Height as 'highTrees.height'
FROM Trees WHERE [Height] > 5
UNION ALL
SELECT 
   Id as 'lowTrees.id',
   Type as 'lowTrees.type',
   Height as 'lowTrees.height'
FROM Trees WHERE [Height] < 1
FOR JSON PATH;

但显然这不是要走的路，因为它给出了这个：

[
  {
    "highTrees": {
      "id": 1,
      "type": "Palm",
      "height": 5.5
    }
  },
  {
    "highTrees": {
      "id": 2,
      "type": "Pine",
      "height": 6.2
    }
  },
  {
    "highTrees": {
      "id": 4,
      "type": "Japanese Cedar",
      "height": 0.5
    }
  },
  {
    "highTrees": {
      "id": 5,
      "type": "Spanish Fir",
      "height": 0.6
    }
  }
]

我怎样才能达到预期的效果？

sql-server json

1 个回答

  1. Best Answer

    在您预期的 JSON 中，highTrees并且lowTrees是键。以及您通常从列中获取的键。因此，这些应该是生成查询中的单独列，而不是同一行集中的单独行子集。

    知道了这一点，您可以UNION像这样修改您的查询（现场演示）：

    SELECT
  highTrees = JSON_QUERY(
                (
                  SELECT 
                  Id as id,
                  Type as type,
                  Height as height
                  FROM Trees WHERE [Height] > 5
                  FOR JSON PATH
                )
              ),

  lowTrees  = JSON_QUERY(
                (
                  SELECT 
                  Id as id,
                  Type as type,
                  Height as height
                  FROM Trees WHERE [Height] < 1
                  FOR JSON PATH
                )
              )
FOR JSON
  PATH, WITHOUT_ARRAY_WRAPPER
;

    并获得（几乎）预期的输出：

    {
  "highTrees": [
    {
      "id": 1,
      "type": "Palm",
      "height": 5.5
    },
    {
      "id": 2,
      "type": "Pine",
      "height": 6.2
    }
  ],
  "lowTrees": [
    {
      "id": 4,
      "type": "Japanese Cedar",
      "height": 0.5
    },
    {
      "id": 5,
      "type": "Spanish Fir",
      "height": 0.6
    }
  ]
}
    • 2

相关问题