Como obter a chave e o valor da string JSON no SQL Server 2017? [duplicado]

Question

Eu tenho uma tabela com várias linhas. Uma coluna desta linha contém string de texto como:

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

Existem algumas condições para a string acima:

1. O valor das subchaves não é constante. Portanto, para uma linha pode ser "ABC" e para outra tecla pode ser "DEF"

2. subSubKey1e subsubkey2, o nome dessas chaves é constante.

Eu tenho que conseguir:

1. Número de subchaves que tenho
2. Valor correspondente a SubSubKey1

Answer 1

Se entendi a pergunta corretamente, uma opção possível é a seguinte afirmação:

JSON:

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

Declaração:

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

Resultado:

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

Answer 2

Isso abre o JSON. Não está claro como contar porque o JSON é fixo, não há matrizes.

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