How to remove 'dbo' from JSON string output?

Question

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?

Answer 1

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;

Answer 2

'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;