Leden | 2022 | Poznámky k T-SQL

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
	}
]

Poznámky k T-SQL

píše Jakub Rychlý

Archiv pro měsíc: Leden 2022

Tvoření JSONu v SQL – FOR JSON

Pár příkladů jak vygenerovat JSON v SQL serveru.

A teď už příklady

Escapování JSONu

Pole hodnot je potřeba slepit ručně

NULL hodnoty