Come ottenere chiave e valore dalla stringa JSON in SQL Server 2017? [duplicare]

Question

Ho una tabella con più righe. Una colonna di questa riga contiene una stringa di testo come:

{ 
   "key1": {}, 
   "key2": { "subKey1": {"subSubKey1":1}, "subkey2": {"subSubKey1":1, "SubSubKey2": 0}}
}

Ci sono alcune condizioni per la stringa precedente:

1. Il valore delle sottochiavi non è costante. Quindi per una riga può essere "ABC" e per l'altra chiave può essere "DEF"

2. subSubKey1e subsubkey2il nome di queste chiavi è costante.

Devo prendere:

1. Numero di sottochiavi che ho
2. Valore corrispondente a SubSubKey1

Answer 1

Se capisco correttamente la domanda, un'opzione possibile è la seguente dichiarazione:

JSON:

DECLARE @json nvarchar(max) = N'{
   "key1": {}, 
   "key2": {"subKey1": {"subSubKey1":1}, "subkey2": {"subSubKey1":1, "SubSubKey2": 0}}
}'

Dichiarazione:

SELECT 
   j1.[key], 
   j2.SubKeyCount, 
   j2.[key] AS SubKey, 
   JSON_VALUE(j2.[value], '$.subSubKey1') AS SubSubKey1
FROM OPENJSON(@json) j1
OUTER APPLY (
   SELECT [key], [value], COUNT(*) OVER (ORDER BY (SELECT NULL)) AS SubKeyCount
   FROM OPENJSON(j1.[value]) 
) j2

Risultato:

key  SubKeyCount SubKey SubSubKey1
----------------------------------
key1            
key2           2 subKey1         1
key2           2 subkey2         1

Answer 2

Questo apre il JSON. Non è chiaro come contare perché il JSON è fisso, non ci sono array.

declare   @json nvarchar(max)=N'{ 
   "key1": {}, 
   "key2": { "subKey1": {"subSubKey1":1}, "subkey2": {"subSubKey1":1, "SubSubKey2": 0}}
}';


select j.key1, sj.subKey1, ssj.subSubKey1, SubSubKey2
from openjson(@json) with (key1            nvarchar(4000),
                           key2            nvarchar(max) as json) j
     cross apply
     openjson(j.Key2) with (subKey1        nvarchar(max) as json,
                            subkey2        nvarchar(max) as json) sj
     cross apply
     openjson(sj.subkey2) with (subSubKey1        int,
                                SubSubKey2       int) ssj