I have following data:
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{
"provider_references": [{"provider_groups": [{"npi": [123456789], "tin": {"type": "ein", "value": "8987654321"}}], "provider_group_id": 0}, {"provider_groups": [{"npi": [123456789], "tin": {"type": "ein", "value": "456789123"}}], "provider_group_id": 1}]
}
]';
SELECT *
FROM OPENJSON(@json)
I want extract npi, provider_group_ID, ein and value. How do I write SQL query.