Convertire un JSON in una tabella SQL con T-SQL / Sgart.it

SQL Server 2016 mette a disposizione delle funzioni per manipolare le stringhe Json.
Ad esempio, avendo una stringa Json, è possibile trattarla come se fosse una tabella tramite l'istruzione OPENJSON:

DECLARE @json nvarchar(max) = '["1","2","3",null, 5]';

SELECT *
FROM OPENJSON( @json );

da come risultato:

key	 value	type
0    1      1
1    2      1
2    3      1
3    NULL   0
4    5      2

per ogni voce dell'array ritorna:

key: l'indice della posizione
value: il valore contenuto
type: il tipo di dato

Per salvare il JSON conviene usare il tipo nvarchar(MAX)

Allo stesso modo è possibile gestire oggetti Json più complessi:

DECLARE @json nvarchar(MAX) = '[
	{"name": "file1.png", "size": "1024"},
	{"name": "file3.jpg", "size": "20488"},
	{"name": "file5.gif", "size": "102"}
]';

SELECT *
FROM OPENJSON( @json )

key  value                                   type
0    {"name": "file1.png", "size": "1024"}   5
1    {"name": "file3.jpg", "size": "20488"}  5
2    {"name": "file5.gif", "size": "102"}    5

Nel caso si volessero solo alcune proprietà esposte come colonna della tabella, è possibile usare la funzione JSON_VALUE:

SELECT JSON_VALUE(x.value, '$.name') as [name],
	JSON_VALUE(x.value, '$.size') as [size]
FROM OPENJSON( @json ) x

il risultato è:

name       size
file1.png  1024
file3.jpg  20488
file5.gif  102

Posso ottenere lo stesso risultato con la keyword WITH:

SELECT *
FROM OPENJSON ( @json )  
WITH (   
  [Name] varchar(150) '$.name',  
  [Size] int '$.size'
 ) AS x

Se il JSON ha delle proprietà annidate, posso selezionarle separando le proprietà con il punto, ad esempio $.prop1.prop2.
Se la proprietà include degli spazi o caratteri speciali, va racchiusa tra doppie virgolette: $.prop1."prop-2".

Vedi anche: OPENJSON (Transact-SQL).