时间戳

首页 > 解决方案 > 菜单结构的 SQL 选择到 JSON

json - 菜单结构的 SQL 选择到 JSON

问题描述

我正在寻求有关查询的帮助，该查询将生成 JSON 格式的输出，我可以使用它来填充菜单，因此 JSON 需要创建菜单结构。

这是我拥有的两个表，我能够编写的最佳查询（它不会产生我需要的输出）是：

SELECT 
    c.ID As CategoryID, 
    c.Name As CategoryName,
    p.ProductName as ProductName
    p.ProductID as ProductID
FROM Category c, Product p
WHERE 
    c.ID = p.CategoryID
FOR JSON PATH, ROOT('Menu')

表：类别

ID   |  Name
---------------
1    |  Fruit
2    |  Vegetable

表：产品

ProductID  |   CategoryID  |  ProductName
----------------------------------------
1           |  1           |  Apple
2           |  1           |  Orange
3           |  2           |  Celery
4           |  2           |  Carrot
5           |  1           |  Banana

此查询的所需输出如下

   menu: 
      [
         {name: 'Fruit', ID: '1', Items: 
            [
               {productname: 'Apple', productid: '1'},
               {productname: 'Orange', productid: '2'},
               {productname: 'Banana', productid: '5'}
            ]
         },
         {name: 'Vegetable', ID: '2', Items: 
            [
               {productname: 'Celery', productid: '3'},
               {productname: 'Carrot', productid: '4'}
            ]
         }
   ]

是否有人能够解释我可以更改查询以实现此目的的步骤？

标签： jsonsql-server

解决方案

您可以在以下选项中进行选择：

  • FOR JSON AUTO和适当的加入。输出是根据SELECT语句的结构自动生成的。
  • 两个嵌套FOR JSON AUTO语句的组合：

桌子：

CREATE TABLE Category (ID int, Name varchar(50))
INSERT INTO Category (ID, Name)
VALUES
   (1, 'Fruit'),
   (2, 'Vegetable')
CREATE TABLE Product (ProductID int, CategoryID int, ProductName varchar(50))
INSERT INTO Product (ProductID, CategoryID, ProductName)
VALUES
   (1, 1, 'Apple'),
   (2, 1, 'Orange'),
   (3, 2, 'Celery'),
   (4, 2, 'Carrot'),
   (5, 1, 'Banana')

语句（使用 FOR JSON AUTO 和适当的连接和选择结构）：

SELECT 
   c.Name AS name, c.ID as id, 
   items.ProductName AS productname, items.ProductId AS productid
FROM Category c
JOIN Product items ON (c.ID = items.CategoryId)
ORDER BY c.ID, items.ProductId
FOR JSON AUTO, ROOT ('menu')

语句（使用两个 FOR JSON AUTO 语句）：

SELECT 
   c.Name AS name, c.ID as id, 
   items = (
      SELECT p.ProductName AS productname, p.ProductID AS productid
      FROM Product p
      WHERE p.CategoryID = c.ID
      FOR JSON AUTO
   )
FROM Category c
FOR JSON AUTO, ROOT ('menu')

结果：

{
  "menu":[
    {
      "name":"Fruit",
      "id":1,
      "items":[
        {"productname":"Apple","productid":1},
        {"productname":"Orange","productid":2},
        {"productname":"Banana","productid":5}
      ]
    },
    {
      "name":"Vegetable",
      "id":2,
      "items":[
        {"productname":"Celery","productid":3},
        {"productname":"Carrot","productid":4}
      ]
    }
  ]
}

推荐阅读