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).