sql - 从 SQL 表中获取嵌套的 JSON
问题描述
下面是我的表格数据的样子。
下面是创建表的 SQL 查询
CREATE TABLE [dbo].[CategoryMaster](
[CategoryId] [int] NOT NULL,
[ParentId] [int] NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_CategoryMaster] PRIMARY KEY CLUSTERED
(
[CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (1, NULL, N'Toys & Games')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (2, 1, N'Art And Crafts')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (3, 1, N'Baby & Toddler Toys')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (4, 1, N'Bikes, Trikes & Ride-Ons')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (5, 2, N'Aprons & Smocks')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (6, 2, N'Blackboards & Whiteboards')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (7, 2, N'Clay & Dough')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (8, 1, N'Pretend Play')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (9, 8, N'Kitchen Toys')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (10, 9, N'Cooking Appliances')
GO
INSERT [dbo].[CategoryMaster] ([CategoryId], [ParentId], [Name]) VALUES (11, 9, N'Cookware')
GO
ALTER TABLE [dbo].[CategoryMaster] WITH CHECK ADD CONSTRAINT [FK_CategoryMaster_CategoryMaster] FOREIGN KEY([ParentId])
REFERENCES [dbo].[CategoryMaster] ([CategoryId])
GO
ALTER TABLE [dbo].[CategoryMaster] CHECK CONSTRAINT [FK_CategoryMaster_CategoryMaster]
GO
解决方案
您可以创建一个递归函数,如下所示:
CREATE FUNCTION Create_Json(@CategoryId INT, @IsRoot INT)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Json NVARCHAR(MAX) = '{}', @Name NVARCHAR(MAX), @Children NVARCHAR(MAX)
SET @Json = (SELECT P.[Name],JSON_QUERY(dbo.Create_Json(P.CategoryId, 2) ) AS Children
FROM [dbo].[CategoryMaster] AS P
WHERE P.ParentId = @CategoryId
FOR JSON AUTO);
IF(@IsRoot = 1)
BEGIN
SELECT @Name = P.[Name] FROM [dbo].[CategoryMaster] AS P WHERE P.CategoryId = @CategoryId
SET @Json = '"result": {"Name":"' + @Name + '","Children":' + CAST(@Json AS NVARCHAR(MAX)) + '}'
SET @IsRoot = 0
END
RETURN @Json
END
并称之为:
select dbo.Create_Json(1, 1)
请在此处找到 db<>fiddle 。
推荐阅读
- php - 如何修复警告:count(): 参数必须是在 C:\wamp64\www\newdatabase\datalist.php 中实现 Countable 的数组或对象,第 983 行
- c# - 如何在 Unity3d 中检测网格碰撞?
- android - 如何在我的设备上自动配对我的 BLE 外设?
- javascript - 如何将文本从 inputtext 传递到 URL?
- symfony - 传递给 *** 的 Symfony 参数 n 必须是 Swift_Mailer 的实例,给定为空
- scala - 如何在失败的情况下使用多个期货
- docker - 运行自定义脚本后 Docker nginx 未启动
- android - 多个开发人员如何在连接到单个 Firebase 控制台的同一个 Android 应用上工作?
- angular - 使用 Web 套接字重试后无法获得成功的连接消息
- apache-kafka - 如何在 RabbitMQ 中实现类似 Kafka 的分发?