如何从 JSON 字符串输出中删除"dbo"？

Question

我试图通过从多个表(个人选择语句)读取数据来生成最终的json字符串。

我有一个表，其中存储SQL查询如下:

EmployeeArchiveTable:

Id     EmployeeId    TableName       SQLQuery
1      1             dbo.Employee    select * from employee where EmployeeID = 1
1      1             dbo.Payroll     select * from Payroll where EmployeeID = 1
1      1             dbo.Leaves      select * from Leaves where EmployeeID = 1
1      1             dbo.Division    select * from Division where EmployeeID = 1

存储过程从上面的表和Employee Id中读取数据，并生成JSON字符串，如下所示:

预期输出:

{
"employeeID 1" : {
"Employee" : { /employee data/}, //either object or array of object based on whatever we get by executing the query
"Payroll"  : { /payroll data/}, //either object or array of object based on whatever we get by executing the query
.
.
} 
}

存储过程:

Create Proc [dbo].[getEmployeeJsonByEmployeeId]
@EmployeeID int
AS
Begin
declare @json varchar(max) = '';

declare my_cursor CURSOr for
select TableName, SQLQuery from EmployeeArchiveTable where employeeID = @employeeID;
declare @tableName varchar(50);
declare @sqlQuery varchar(max);

Fetch next from my_cursor into @tableName,@sqlQuery;

while @@FETCH_STATUS = 0
Begin
select @json += 'Json_Query((' + sqlQuery + ')) as ' + '[' + (@tableName) + '] ' + N', ';
fetch next from my_cursor into @tableName, @sqlQuery;
End
close my_cursor;
select @json = 'select ' + substring(@json,1,LEN(@json) - 1) + ' FOR JSON Path, WITHOUT_ARRAY_WRAPPER';
print @json;
select @json;
End;

输出最终SQL查询:

select Json_Query((select * from employee where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Employee],
Json_Query((select * from Payroll where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Payroll],
Json_Query((select * from Leaves where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Leaves],
Json_Query((select * from Division where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Division] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

生成JSON输出:

{
"dbo" : {
"Employee" : { /employee data/}, //either object or array of object based on whatever we get by executing the query
"Payroll"  : { /payroll data/}, //either object or array of object based on whatever we get by executing the query
}
}

我不确定这个'dbo'在JSON中来自哪里以及我如何删除它?

Answer 1

dbo在您的最后查询(例如[dbo.Employee])

select Json_Query((select * from employee where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Employee],
Json_Query((select * from Payroll where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Payroll],
Json_Query((select * from Leaves where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Leaves],
Json_Query((select * from Division where EmployeeID = 1 FOR JSON path, INCLUDE_NULL_VALUES)) as [dbo.Division] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

如果您想删除它，那么替换'dbo。在@tableName中。我已经修改了你的代码

Create Proc [dbo].[getEmployeeJsonByEmployeeId]@EmployeeID int作为开始声明@json varchar(max) = ";

declare my_cursor CURSOr for
select TableName, SQLQuery from EmployeeArchiveTable where employeeID = @employeeID;
declare @tableName varchar(50);
declare @sqlQuery varchar(max);

Fetch next from my_cursor into @tableName,@sqlQuery;

while @@FETCH_STATUS = 0
Begin
select @json += 'Json_Query((' + sqlQuery + ')) as ' + '[' + (replace(@tableName,'dbo.','') + '] ' + N', ';
fetch next from my_cursor into @tableName, @sqlQuery;
End
close my_cursor;
select @json = 'select ' + substring(@json,1,LEN(@json) - 1) + ' FOR JSON Path, WITHOUT_ARRAY_WRAPPER';
print @json;
select @json;

结束;

Answer 2

'dbo'来自EmployeeArchivalTable中的模式。当表名被存储时(dbo。Employee等)，模式名包含在JSON属性名中。
如果你在while循环中添加一行来代替dbo部分，那应该会有帮助。

````
set @tableName = REPLACE(@tableName, 'dbo.', '');
````

添加quotename，以确保JSON中的表名格式正确。

在本地测试了这个，它得到了你想要的:

CREATE PROCEDURE [dbo].[getEmployeeJsonByEmployeeId]
@EmployeeID int
AS
Begin
declare @json varchar(max) = '';
declare my_cursor CURSOR for
select TableName, SQLQuery from EmployeeArchiveTable where EmployeeID = @employeeID;
declare @tableName varchar(50);
declare @sqlQuery varchar(max);
Fetch next from my_cursor into @tableName, @sqlQuery;
while @@FETCH_STATUS = 0
Begin
-- Remove the schema name (dbo.) from the table name
set @tableName = REPLACE(@tableName, 'dbo.', '');
select @json += 'Json_Query((' + @sqlQuery + ' FOR JSON path, INCLUDE_NULL_VALUES)) as ' + QUOTENAME(@tableName) + N', ';
fetch next from my_cursor into @tableName, @sqlQuery;
End
close my_cursor;
select @json = 'select ' + substring(@json,1,LEN(@json) - 1) + ' FOR JSON Path, WITHOUT_ARRAY_WRAPPER';
print @json;
EXEC (@json); -- Execute the dynamic SQL to return the JSON
End;