How to remove 'dbo' from JSON string output?

I am trying to generate the final json string by reading data from multiple tables(Individual Select statement).

I have a table which stores the SQL query like below:

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

Stored Procedure to read the data from above table and Employee Id and generate JSON string like below:

Expected Output:

{
  "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
  .
  .
  } 
}

Stored Procedure :

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;

Output final SQL Query:

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

This produce JSON output:

{
  "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
  }
}

I am not sure where this 'dbo' is coming from in JSON and how do I remove it?

Solution

dbo is in your final query (for example [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

if you want to remove it then replace 'dbo.' in the @tableName. I have modified your code to do so

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 ' + '[' + (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;

End;