Čtení JSON formátu v SQL serveru

Pro JSON není v SQL serveru spciální datový typ. Používá se NVARCHAR(MAX), JSON formát můžu vynutit constraintem pomocí funkce ISJSON.


Jako přiklad si udělám tempovou tabulku s constraintem

CREATE TABLE #json(
	JSONData NVARCHAR(MAX) NOT NULL
		CONSTRAINT check_JSONData_is_json CHECK (ISJSON(JSONData) = 1)
)
GO



Pokusím se vložit něco, co není JSON

INSERT INTO #json(JSONData) VALUES('nesmysl')
GO

--The INSERT statement conflicted with the CHECK constraint "check_JSONData_is_json". The conflict occurred in database "tempdb", table "dbo.#json_______________________________________________________________________________________________________________000000000067", column 'JSONData'.



Založím testovací data

INSERT INTO #json(JSONData) VALUES(
N'
[
	{
		"jmeno": "Kamil",
		"prijmeni": "Skočdopole",
		"konicky": [
			"šachy",
			"sport",
			"vážná hudba"
		],
		"adresy": [
			{
				"typ": "bydliště",
				"mesto": "Frenštát pod Radhoštěm",
				"cp": "10"
			},
			{
				"typ": "doručovací",
				"mesto": "Ostrava",
				"cp": "50"
			}
		],
		"hodne pije": true,
		"telesna_teplota": 36.9,
		"zbrojni_pas":null
	},
	{
		"jmeno": "Uršula",
		"prijmeni": "Králová",
		"konicky": [
			"pletení",
			"ultramaraton"
		],
		"adresy": [
			{
				"typ": "bydliště",
				"mesto": "Frenštát pod Radhoštěm",
				"cp": "10"
			}
		]
	}
]
'
)

SELECT * FROM #json



Otestování, zda máme validní JSON

SELECT ISJSON((SELECT * FROM #json))--vrací 1



JSON_QUERY vrací JSON objekt, nebo pole

SELECT JSON_QUERY((SELECT * FROM #json))
SELECT JSON_QUERY((SELECT * FROM #json), '$[0].adresy')
SELECT JSON_QUERY((SELECT * FROM #json), '$[1].adresy')
SELECT JSON_QUERY((SELECT * FROM #json), '$[1].konicky')



Striktní vs. laxní mód

By deafault je laxní

--NULL, protoze jmeno je hodnota a ne objekt
SELECT JSON_QUERY((SELECT * FROM #json), '$[0].jmeno')

--NULL, protoze jmeno je hodnota a ne objekt
SELECT JSON_QUERY((SELECT * FROM #json), 'lax $[0].jmeno')

--Object or array cannot be found in the specified JSON path.
SELECT JSON_QUERY((SELECT * FROM #json), 'strict $[0].jmeno')



JSON_VALUE vrací hodnotu NVARCHAR(4000)

SELECT JSON_VALUE((SELECT * FROM #json), '$[0].jmeno')--Kamil



Pokud je v názvu atributu mezera, tak ho musím mít v uvozovkách

--JSON path is not properly formatted. Unexpected character ' ' is found at position 10.
SELECT JSON_VALUE((SELECT * FROM #json), '$[0].hodne pije')

--true
SELECT JSON_VALUE((SELECT * FROM #json), '$[0]."hodne pije"')



Neexistující atribut je problém rozeznat od atributu s NULL hodnotou

--NULL
SELECT JSON_VALUE((SELECT * FROM #json), '$[0].neexistujici')

--Property cannot be found on the specified JSON path.
SELECT JSON_VALUE((SELECT * FROM #json), 'strict $[0].neexistujici')



Atribut s NULL hodnotou

--NULL
SELECT JSON_VALUE((SELECT * FROM #json), '$[0].zbrojni_pas')

--NULL
SELECT JSON_VALUE((SELECT * FROM #json), 'strict $[0].zbrojni_pas')

OPENJSON

Další možností čtení JSONu je s použitím funkce OPENJSON. Umožňuje přečíst více než 4000 znaků a vracet data v tabulkové podobě.

Syntaxe je:

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

SELECT *
FROM OPENJSON((SELECT * FROM #json))

Výsledkem je tabulka se sloupci key, value, type
openjson1

key
název atributu, nebo pořadové číslo v poli
value
hodnota
type
0 NULL Rozumná cesta jak rozlišit hodnotu NULL od neexistujícího atributu
1 string
2 číslo
3 boolean
4 pole
5 objekt

Dá se k tomu doplnit cesta

SELECT *
FROM OPENJSON((SELECT * FROM #json),'$[0]')

openjson2

SELECT *
FROM OPENJSON((SELECT * FROM #json),'$[0].konicky')

openjson3

Doplněním WITH kluzule uděláme z JSON tabulku, kde budou atributy ve sloupcích


Syntaxe:

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50),
	prijmeni		NVARCHAR(50),
	[hodne pije]	BIT,
	nesmysl			NVARCHAR(50),
	zbrojni_pas		BIT
)

openjson4

Můžeme doplnit column_path a přejmenovat si na vlastní colName

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50)	'$.jmeno',
	prijmeni		NVARCHAR(50)	'$.prijmeni',
	[ochlasta]		BIT				'$."hodne pije"',
	nesmysl			NVARCHAR(50)	'$.nesmysl',
	zbrojni_pas		BIT				'$.zbrojni_pas',
	prvniKonicek	NVARCHAR(250)	'$.konicky[0]',
	mesto			NVARCHAR(250)	'$.adresy[0].mesto'
)

openjson5
Pokud chceme vrátit JSON, nebo pole, tak musíme použít AS JSON, jinak dostaneme NULL

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50)	'$.jmeno',
	prijmeni		NVARCHAR(50)	'$.prijmeni',
	[ochlasta]		BIT				'$."hodne pije"',
	nesmysl			NVARCHAR(50)	'$.nesmysl',
	zbrojni_pas		BIT				'$.zbrojni_pas',
	adresy1			NVARCHAR(MAX)	'$.adresy',				--pokud je tam objekt, nebo pole, 
															--tak nevrátí nic v lax modu. strict by spadnul
	adresy2			NVARCHAR(MAX)	'$.adresy' AS JSON		--je potřeba použít AS JSON
)

openjson6

Vrácený JSON z první funkce můžeme dál rozebrat pomocí APPLY OPENSJON

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50)	'$.jmeno',
	prijmeni		NVARCHAR(50)	'$.prijmeni',
	[ochlasta]		BIT				'$."hodne pije"',
	nesmysl			NVARCHAR(50)	'$.nesmysl',
	zbrojni_pas		BIT				'$.zbrojni_pas',
	adresy			NVARCHAR(MAX)	'$.adresy' AS JSON
)pole
OUTER APPLY OPENJSON(pole.adresy) WITH(
	typ			NVARCHAR(50)	,
	mesto		NVARCHAR(50)	,
	cp			NVARCHAR(50)
)

openjson7