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?

'dbo' comes from the schema in in the EmployeeArchivalTable. When the table names are stored(dbo.Employee, etc.) the schema name is included in the JSON property name.
If you add a line in the while loop to replace the dbo part that should help.

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

Added quotename too to make sure the table names in the JSON are correctly formatted.

Tested this locally and it gets what you were looking for:

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;

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;