I would like you to help me I am making a json in sql server, I have joined two queries but when I exit I get " and \ as below the result
CREATE TABLE [dbo].[tablajson](
[level] [int] NULL,
[columnDef] [varchar](250) NULL,
[header] [varchar](250) NULL,
[isdata] [int] NULL,
[type] [varchar](100) NULL,
[mode] [varchar](100) NULL,
[orden] [int] NULL,
[orden2] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_10', N'Total semanal clientes nuevos', 11, N'number', N'.0-0', 4, 10)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_10', N'Meta semanal clientes nuevos', 12, N'number', N'.0-0', 4, 11)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_10', N'% Avance semanal', 13, N'percent', NULL, 4, 12)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'EXT_10', N'Total Acumulado en el Mes', 9, N'number', N'.0-0', 4, 8)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'ACU_10', N'Meta acumulada del mes', 10, N'number', N'.0-0', 4, 9)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_10', N'Total Acumulado en el Mes, mes anterior', 8, N'number', N'.0-0', 4, 7)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_11', N'Total semanal prospectos', 5, N'number', N'.0-0', 4, 4)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_11', N'Meta semanal prospectos', 6, N'number', N'.0-0', 4, 5)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_11', N'% Avance semanal', 7, N'percent', NULL, 4, 6)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'EXT_11', N'Total Acumulado en el Mes', 3, N'number', N'.0-0', 4, 2)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'ACU_11', N'Meta acumulada del mes', 4, N'number', N'.0-0', 4, 3)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_11', N'Total Acumulado en el Mes, mes anterior', 2, N'number', N'.0-0', 4, 1)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_18', N'Clientes desembolsados', 27, N'number', N'.0-0', 4, 26)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_18', N'Total clientes', 28, N'number', N'.0-0', 4, 27)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_18', N'% Efectividad', 29, N'percent', NULL, 4, 28)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_18', N'% Efectividad, mes anterior', 26, N'percent', NULL, 4, 25)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_19', N'Clientes desembolsados', 31, N'number', N'.0-0', 4, 30)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_19', N'Total clientes', 32, N'number', N'.0-0', 4, 31)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_19', N'% Efectividad', 33, N'percent', NULL, 4, 32)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_19', N'% Efectividad, mes anterior', 30, N'percent', NULL, 4, 29)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_500', N'Saldo recuperado', 35, N'number', N'.0-0', 4, 34)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_500', N'Saldo total', 36, N'number', N'.0-0', 4, 35)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_500', N'% Efectividad', 37, N'percent', NULL, 4, 36)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_500', N'% Efectividad, mes anterior', 34, N'percent', NULL, 4, 33)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_60', N'Saldo recuperado', 39, N'number', N'.0-0', 4, 38)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_60', N'Saldo total', 40, N'number', N'.0-0', 4, 39)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_60', N'% Efectividad', 41, N'percent', NULL, 4, 40)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_60', N'% Efectividad, mes anterior', 38, N'percent', NULL, 4, 37)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_7', N'Clientes desembolsados', 15, N'number', N'.0-0', 4, 14)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_7', N'Total clientes', 16, N'number', N'.0-0', 4, 15)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_7', N'% Efectividad', 17, N'percent', NULL, 4, 16)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_7', N'% Efectividad, mes anterior', 14, N'percent', NULL, 4, 13)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_8', N'Clientes desembolsados', 19, N'number', N'.0-0', 4, 18)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_8', N'Total clientes', 20, N'number', N'.0-0', 4, 19)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_8', N'% Efectividad', 21, N'percent', NULL, 4, 20)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_8', N'% Efectividad, mes anterior', 18, N'percent', NULL, 4, 17)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_9', N'Clientes desembolsados', 23, N'number', N'.0-0', 4, 22)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DEN_9', N'Total clientes', 24, N'number', N'.0-0', 4, 23)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'DIV_9', N'% Efectividad', 25, N'percent', NULL, 4, 24)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_9', N'% Efectividad, mes anterior', 22, N'percent', NULL, 4, 21)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_10', N'CLIENTES NUEVOS', NULL, NULL, NULL, 2, 7)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_11', N'PROSPECTOS', NULL, NULL, NULL, 2, 1)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_18', N'EFECTIVIDAD CANCELADOS PRIORIDAD 2', NULL, NULL, NULL, 2, 25)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_19', N'EFECTIVIDAD AMPLIACIÓN PRIORIDAD 2', NULL, NULL, NULL, 2, 29)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_500', N'CARTERA TRAMO -30-0 D', NULL, NULL, NULL, 2, 33)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_60', N'CARTERA TRAMO 1-30 D', NULL, NULL, NULL, 2, 37)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_7', N'EFECTIVIDAD CRÉDITOS AUTOMÁTICOS', NULL, NULL, NULL, 2, 13)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_8', N'EFECTIVIDAD CANCELADOS', NULL, NULL, NULL, 2, 17)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_9', N'EFECTIVIDAD AMPLIACIÓN', NULL, NULL, NULL, 2, 21)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_10', N'2 clientes nuevos x asesor', NULL, NULL, NULL, 1, 7)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_11', N'15 prospectos x asesor', NULL, NULL, NULL, 1, 1)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_18', N'8%', NULL, NULL, NULL, 1, 25)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_19', N'8%', NULL, NULL, NULL, 1, 29)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_500', N'95%', NULL, NULL, NULL, 1, 33)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_60', N'55%', NULL, NULL, NULL, 1, 37)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_7', N'12%', NULL, NULL, NULL, 1, 13)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_8', N'12%', NULL, NULL, NULL, 1, 17)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_9', N'12%', NULL, NULL, NULL, 1, 21)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_10', N'17-12-2019', NULL, NULL, NULL, 3, 7)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_11', N'17-12-2019', NULL, NULL, NULL, 3, 1)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_18', N'17-12-2019', NULL, NULL, NULL, 3, 25)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_19', N'17-12-2019', NULL, NULL, NULL, 3, 29)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_500', N'17-12-2019', NULL, NULL, NULL, 3, 33)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_60', N'17-12-2019', NULL, NULL, NULL, 3, 37)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_7', N'17-12-2019', NULL, NULL, NULL, 3, 13)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_8', N'17-12-2019', NULL, NULL, NULL, 3, 17)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'AN_9', N'17-12-2019', NULL, NULL, NULL, 3, 21)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'EXT_10', N'20-01-2020', NULL, NULL, NULL, 3, 8)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'EXT_11', N'20-01-2020', NULL, NULL, NULL, 3, 2)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_18', N'20-01-2020', NULL, NULL, NULL, 3, 26)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_19', N'20-01-2020', NULL, NULL, NULL, 3, 30)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_500', N'20-01-2020', NULL, NULL, NULL, 3, 34)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_60', N'20-01-2020', NULL, NULL, NULL, 3, 38)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_7', N'20-01-2020', NULL, NULL, NULL, 3, 14)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_8', N'20-01-2020', NULL, NULL, NULL, 3, 18)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'NUM_9', N'20-01-2020', NULL, NULL, NULL, 3, 22)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'descripcion', N'Descripción', 1, NULL, NULL, 2, 2)
INSERT [dbo].[tablajson] ([level], [columnDef], [header], [isdata], [type], [mode], [orden], [orden2]) VALUES (1, N'hitos', N'Hitos de la semana 3', NULL, NULL, NULL, 1, 1)
select distinct pr.orden as level,
(
select tb.columnDef as [columnDef],
tb.header as [header],
tb.isdata as [isdata],
tb.type as [format.type],
tb.mode as [format.mode]
from #Tabla2 tb
where tb.orden = pr.orden
FOR JSON path
) as columns
FROM #Tabla2 pr
order by orden asc
FOR JSON path
The following result comes out.
[
{
"level": 1,
"columns": [
{
"columnDef": "AN_10",
"header": "2 clientes nuevos x asesor"
},
{
"columnDef": "AN_11",
"header": "15 prospectos x asesor"
},
{
"columnDef": "AN_18",
"header": "8%"
},
{
"columnDef": "AN_19",
"header": "8%"
},
{
"columnDef": "AN_500",
"header": "95%"
},
{
"columnDef": "AN_60",
"header": "55%"
},
{
"columnDef": "AN_7",
"header": "12%"
},
{
"columnDef": "AN_8",
"header": "12%"
},
{
"columnDef": "AN_9",
"header": "12%"
},
{
"columnDef": "hitos",
"header": "Hitos de la semana 3"
}
]
},
{
"level": 2,
"columns": [
{
"columnDef": "AN_10",
"header": "CLIENTES NUEVOS"
},
{
"columnDef": "AN_11",
"header": "PROSPECTOS"
},
{
"columnDef": "AN_18",
"header": "EFECTIVIDAD CANCELADOS PRIORIDAD 2"
},
{
"columnDef": "AN_19",
"header": "EFECTIVIDAD AMPLIACIÓN PRIORIDAD 2"
},
{
"columnDef": "AN_500",
"header": "CARTERA TRAMO -30-0 D"
},
{
"columnDef": "AN_60",
"header": "CARTERA TRAMO 1-30 D"
},
{
"columnDef": "AN_7",
"header": "EFECTIVIDAD CRÉDITOS AUTOMÁTICOS"
},
{
"columnDef": "AN_8",
"header": "EFECTIVIDAD CANCELADOS"
},
{
"columnDef": "AN_9",
"header": "EFECTIVIDAD AMPLIACIÓN"
},
{
"columnDef": "descripcion",
"header": "Descripción",
"isdata": 1
}
]
},
{
"level": 3,
"columns": [
{
"columnDef": "AN_10",
"header": "17-12-2019"
},
{
"columnDef": "AN_11",
"header": "17-12-2019"
},
{
"columnDef": "AN_18",
"header": "17-12-2019"
},
{
"columnDef": "AN_19",
"header": "17-12-2019"
},
{
"columnDef": "AN_500",
"header": "17-12-2019"
},
{
"columnDef": "AN_60",
"header": "17-12-2019"
},
{
"columnDef": "AN_7",
"header": "17-12-2019"
},
{
"columnDef": "AN_8",
"header": "17-12-2019"
},
{
"columnDef": "AN_9",
"header": "17-12-2019"
},
{
"columnDef": "EXT_10",
"header": "20-01-2020"
},
{
"columnDef": "EXT_11",
"header": "20-01-2020"
},
{
"columnDef": "NUM_18",
"header": "20-01-2020"
},
{
"columnDef": "NUM_19",
"header": "20-01-2020"
},
{
"columnDef": "NUM_500",
"header": "20-01-2020"
},
{
"columnDef": "NUM_60",
"header": "20-01-2020"
},
{
"columnDef": "NUM_7",
"header": "20-01-2020"
},
{
"columnDef": "NUM_8",
"header": "20-01-2020"
},
{
"columnDef": "NUM_9",
"header": "20-01-2020"
}
]
},
{
"level": 4,
"columns": [
{
"columnDef": "NUM_10",
"header": "Total semanal clientes nuevos",
"isdata": 11,
"format": {
"type": "number",
"mode": ".0-0"
}
},
{
"columnDef": "DEN_10","
I would like the result to be like this.
[
{
"level":1,
"columns":[
{
"columnDef":"DESCRIP",
"header":"Descripción",
"isdata":1,
"sticky":true,
"format":{
"type":"string"
},
"rows":2
},
{
"columnDef":"SSALMN",
"header":"Saldo Puntual Captaciones",
"isdata":2,
"format":{
"type":"number",
"mode":".0-0"
},
"rows":2
}
]
},
{
"level": 2,
"columns":[
{
"columnDef":"DESCRIP",
"header":"Descripción",
"isdata":1,
"sticky":true,
"format":{
"type":"string"
},
"rows":2
},
{
"columnDef":"SSALMN",
"header":"Saldo Puntual Captaciones",
"isdata":2,
"format":{
"type":"number",
"mode":".0-0"
},
"rows":2
}
]
}
]
The problem is, that it
UNION
renders the result with the outputjson
already converted to text, hence theFOR JSON auto
ending is "escaping" the quotes.I understand that what you are looking for, you could solve it like this:
I add an important clarification from your comments, the output
FOR JSON
varies according to the final size, here it is documented that the result can be divided into multiple rows. There are several alternatives to "put together" the final content, here are some possibilities