Pár příkladů jak vygenerovat JSON v SQL serveru.
Nejdřív tabulky a data, které se pak budeme snažit vysypat ve formátu JSON.
IF OBJECT_ID('dbo.Kategorie') IS NULL
CREATE TABLE dbo.Kategorie(
IdKategorie INT NOT NULL IDENTITY CONSTRAINT pk_Kategorie PRIMARY KEY CLUSTERED,
Kod NVARCHAR(50) NOT NULL CONSTRAINT uq_Kategorie_Kod UNIQUE,
Nazev NVARCHAR(250) NOT NULL,
PlatnostOd DATETIME NOT NULL,
PlatnostDo DATETIME NOT NULL
)
GO
IF OBJECT_ID('dbo.KategorieTyp') IS NULL
CREATE TABLE dbo.KategorieTyp(
IdKategorieTyp INT NOT NULL IDENTITY CONSTRAINT pk_KategorieTyp PRIMARY KEY CLUSTERED,
IdKategorie INT NOT NULL CONSTRAINT fk_KategorieTyp_Kategorie FOREIGN KEY (IdKategorie) REFERENCES dbo.Kategorie(IdKategorie),
Kod NVARCHAR(50) NOT NULL CONSTRAINT uq_KategorieTyp_Kod UNIQUE,
Nazev NVARCHAR(250) NOT NULL,
PlatnostOd DATETIME NOT NULL,
PlatnostDo DATETIME NOT NULL
)
GO
INSERT INTO dbo.Kategorie
(
Kod,
Nazev,
PlatnostOd,
PlatnostDo
)
SELECT
data.Kod
,data.Nazev
,GETDATE()
,'20991231'
FROM (VALUES
(N'domacnost' ,N'domácnost' ),
(N'podnikatel' ,N'podnikatel' )
)data(Kod ,Nazev)
WHERE NOT EXISTS(
SELECT *
FROM dbo.Kategorie k
WHERE k.Kod = data.Kod
)
GO
INSERT INTO dbo.KategorieTyp
(
IdKategorie,
Kod,
Nazev,
PlatnostOd,
PlatnostDo
)
SELECT
k.IdKategorie
,data.Kod
,data.Nazev
,GETDATE()
,'20991231'
FROM (VALUES
(N'domacnost' ,N'dum' ,N'dům' ),
(N'domacnost' ,N'byt' ,N'byt' ),
(N'domacnost' ,N'chata' ,N'chata' ),
(N'podnikatel' ,N'garaz' ,N'garáž' ),
(N'podnikatel' ,N'provozovna' ,N'provozovna' ),
(N'podnikatel' ,N'kancelar' ,N'kacelář' )
)data(KategorieKod ,Kod ,Nazev)
JOIN dbo.Kategorie k ON data.KategorieKod = k.Kod
WHERE NOT EXISTS(
SELECT *
FROM dbo.KategorieTyp kt
WHERE kt.Kod = data.Kod
)
GO
A teď už příklady
--Ručně s použitím FOR JSON PATH
SELECT k.IdKategorie,
k.Kod,
k.Nazev,
(
SELECT
kt.IdKategorieTyp,
kt.Kod,
kt.Nazev,
kt.PlatnostOd,
kt.PlatnostDo
FROM dbo.KategorieTyp kt
WHERE kt.IdKategorie = k.IdKategorie
FOR JSON PATH
)typ
FROM dbo.Kategorie k
FOR JSON PATH
--automaticky s použitím FOR JSON AUTO
SELECT k.IdKategorie,
k.Kod,
k.Nazev,
k.PlatnostOd,
k.PlatnostDo,
typ.IdKategorieTyp,
typ.Kod,
typ.Nazev,
typ.PlatnostOd,
typ.PlatnostDo
FROM dbo.Kategorie k
JOIN dbo.KategorieTyp typ ON typ.IdKategorie = k.IdKategorie
FOR JSON AUTO
Výsledek je stejný v obou případech.
[
{
"IdKategorie": 1,
"Kod": "domacnost",
"Nazev": "domácnost",
"PlatnostOd": "2022-01-29T14:47:43.493",
"PlatnostDo": "2099-12-31T00:00:00",
"typ": [
{
"IdKategorieTyp": 1,
"Kod": "dum",
"Nazev": "dům",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
},
{
"IdKategorieTyp": 2,
"Kod": "byt",
"Nazev": "byt",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
},
{
"IdKategorieTyp": 3,
"Kod": "chata",
"Nazev": "chata",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
}
]
},
{
"IdKategorie": 2,
"Kod": "podnikatel",
"Nazev": "podnikatel",
"PlatnostOd": "2022-01-29T14:47:43.493",
"PlatnostDo": "2099-12-31T00:00:00",
"typ": [
{
"IdKategorieTyp": 4,
"Kod": "garaz",
"Nazev": "garáž",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
},
{
"IdKategorieTyp": 5,
"Kod": "provozovna",
"Nazev": "provozovna",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
},
{
"IdKategorieTyp": 6,
"Kod": "kancelar",
"Nazev": "kacelář",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
}
]
}
]
Pokud se chceme zbavit pole a vrátit jen jeden objekt, pak přidáme WITHOUT_ARRAY_WRAPPER.
SELECT TOP 1 * FROM dbo.Kategorie k FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Zanořený jeden objekt dělá problémy.
SELECT k.IdKategorie,
k.Kod,
k.Nazev,
(
SELECT TOP 1
kt.IdKategorieTyp,
kt.Kod,
kt.Nazev,
kt.PlatnostOd,
kt.PlatnostDo
FROM dbo.KategorieTyp kt
WHERE kt.IdKategorie = k.IdKategorie
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)Typ
FROM dbo.Kategorie k
FOR JSON PATH
Výsledek subselectu se projeví jako escapovaný řetězec, což není zpravidla to, čeho chceme dosáhnout.
[
{
"IdKategorie": 1,
"Kod": "domacnost",
"Nazev": "domácnost",
"Typ": "{\"IdKategorieTyp\":1,\"Kod\":\"dum\",\"Nazev\":\"dům\",\"PlatnostOd\":\"2022-01-29T14:47:43.540\",\"PlatnostDo\":\"2099-12-31T00:00:00\"}"
},
{
"IdKategorie": 2,
"Kod": "podnikatel",
"Nazev": "podnikatel",
"Typ": "{\"IdKategorieTyp\":4,\"Kod\":\"garaz\",\"Nazev\":\"garáž\",\"PlatnostOd\":\"2022-01-29T14:47:43.540\",\"PlatnostDo\":\"2099-12-31T00:00:00\"}"
}
]
Pomůže finta s funkcí JSON_QUERY.
SELECT k.IdKategorie,
k.Kod,
k.Nazev,
(SELECT JSON_QUERY((
SELECT TOP 1
kt.IdKategorieTyp,
kt.Kod,
kt.Nazev,
kt.PlatnostOd,
kt.PlatnostDo
FROM dbo.KategorieTyp kt
WHERE kt.IdKategorie = k.IdKategorie
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)))Typ
FROM dbo.Kategorie k
FOR JSON PATH
Výsledek už pak vypadá dle očekávání.
[
{
"IdKategorie": 1,
"Kod": "domacnost",
"Nazev": "domácnost",
"Typ": {
"IdKategorieTyp": 1,
"Kod": "dum",
"Nazev": "dům",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
}
},
{
"IdKategorie": 2,
"Kod": "podnikatel",
"Nazev": "podnikatel",
"Typ": {
"IdKategorieTyp": 4,
"Kod": "garaz",
"Nazev": "garáž",
"PlatnostOd": "2022-01-29T14:47:43.540",
"PlatnostDo": "2099-12-31T00:00:00"
}
}
]
Escapování JSONu
SELECT STRING_ESCAPE('"Nějaká důmyslná data \ /
druhý řádek"','json')
\"Nějaká důmyslná data \\ \/ \r\ndruhý řádek\"
Pole hodnot je potřeba slepit ručně
--SQL2017 a vyšší
SELECT '['+STRING_AGG('"' + STRING_ESCAPE(kt.Kod,'json') + '"', ',')+']'
FROM dbo.KategorieTyp kt
--SQL2016
SELECT '['+STUFF((
SELECT
',"' + STRING_ESCAPE(kt.Kod,'json') + '"'
FROM dbo.KategorieTyp kt
FOR XML PATH('')
),1,1,'')+']'
[ "byt", "dum", "garaz", "chata", "kancelar", "provozovna" ]
Pole jako atribut
--zapracuju jako atribut JSONu pomocí funkce JSON_QUERY
SELECT k.IdKategorie,
k.Kod,
k.Nazev,
JSON_QUERY((
SELECT '['+STUFF((
SELECT
',"' + STRING_ESCAPE(kt.Kod,'json') + '"'
FROM dbo.KategorieTyp kt
WHERE kt.IdKategorie = k.IdKategorie
FOR XML PATH('')
),1,1,'')+']'
))Typy
FROM dbo.Kategorie k
FOR JSON PATH
[
{
"IdKategorie": 1,
"Kod": "domacnost",
"Nazev": "domácnost",
"Typy": [
"dum",
"byt",
"chata"
]
},
{
"IdKategorie": 2,
"Kod": "podnikatel",
"Nazev": "podnikatel",
"Typy": [
"garaz",
"provozovna",
"kancelar"
]
}
]
NULL hodnoty
SELECT *
FROM (
VALUES
('Kamil' , 'Novák'),
('Jarmila' , NULL)
)a (jmeno , prijmeni)
FOR JSON PATH
[
{
"jmeno": "Kamil",
"prijmeni": "Novák"
},
{
"jmeno": "Jarmila"
}
]
SELECT *
FROM (
VALUES
('Kamil' , 'Novák'),
('Jarmila' , NULL)
)a (jmeno , prijmeni)
FOR JSON PATH, INCLUDE_NULL_VALUES
[
{
"jmeno": "Kamil",
"prijmeni": "Novák"
},
{
"jmeno": "Jarmila",
"prijmeni": null
}
]