By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @json NVARCHAR(MAX) = '{
"_key": {
"id": "3b8d1ed7-c3ec-32ec-a083-2ddc17152e94",
"rootId": "15c85327-9628-3685-b84a-375b546ba92a"
},
"employeeInfo": {
"idNumber": "3",
"gender": "Male",
"active": true,
"age": 20
},
"product": {
"plan": "prod",
"class": "1",
"available": true,
"_type": "Product"
}
}';
SELECT
employeeInfo = JSON_QUERY((
SELECT
'{' +
STRING_AGG(
CONCAT(
'"',
STRING_ESCAPE(j.[key], 'json'),
'":',
IIF(j.type = 1, CONCAT('"', STRING_ESCAPE(j.value, 'json'), '"'), j.value)
),
','
) + '}'
FROM OPENJSON(@json, '$.employeeInfo') j
WHERE j.[key] IN ('age', 'gender')
)),
product = JSON_QUERY(@json, '$.product')
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| {"employeeInfo":{"gender":"Male","age":20},"product":{ "plan": "prod", "class": "1", "available": true, "_type": "Product" }} |