Sorry for my bad english and low vocabulary. How to filter-out all fields with "Properties.Searchable" = "true"
? The fields can be a child field of another.
Here is an example(removed most of unnecessary data):
{
"Configuration": {
"Fields": {
"Id": {
"Properties": {
"DataType": "string",
"Searchable": "true"
}
},
"PrsonalInfo": {
"BirthDate": {
"Properties": {
"DataType": "date",
"Searchable": "false"
}
},
"Name": {
"GivenName": {
"Properties": {
"DataType": "string",
"Searchable": "true"
}
},
"FamilyName": {
"Properties": {
"DataType": "string",
"Searchable": "true"
}
}
}
}
}
}
}
I need to query the "Configuration.Fields"
and only return the path of those that have "Properties.Searchable" = "true"
. And the result should be like or close to this:
Id
PersonalInfo.Name.GivenName
PersonalInfo.Name.FamilyName
Thanks!
I hope it's not a late answer. A possible approach (to parse the input JSON and get the expected results) is the following recursive CTE:
JSON:
DECLARE @json nvarchar(1000) = N'{
"Configuration":{
"Fields":{
"Id":{
"Properties":{
"DataType":"string",
"Searchable":"true"
}
},
"PrsonalInfo":{
"BirthDate":{
"Properties":{
"DataType":"date",
"Searchable":"false"
}
},
"Name":{
"GivenName":{
"Properties":{
"DataType":"string",
"Searchable":"true"
}
},
"FamilyName":{
"Properties":{
"DataType":"string",
"Searchable":"true"
}
}
}
}
}
}
}'
Statement:
;WITH rCTE AS (
SELECT
CONVERT(nvarchar(max), N'$') COLLATE DATABASE_DEFAULT AS JsonPath,
CONVERT(nvarchar(max), N'$') COLLATE DATABASE_DEFAULT AS JsonKey,
CONVERT(nvarchar(max), JSON_QUERY(@json, '$.Configuration.Fields')) COLLATE DATABASE_DEFAULT AS JsonValue
UNION ALL
SELECT
CONVERT(nvarchar(max), CONCAT(r.JsonPath, CONCAT(N'.', c.[key]))) COLLATE DATABASE_DEFAULT,
CONVERT(nvarchar(max), c.[key]) COLLATE DATABASE_DEFAULT,
CONVERT(nvarchar(max), c.[value]) COLLATE DATABASE_DEFAULT
FROM rCTE r
CROSS APPLY OPENJSON(r.JsonValue) c
WHERE ISJSON(r.JsonValue) = 1
)
SELECT JsonPath
FROM rCTE
WHERE
CASE
WHEN ISJSON(JsonValue) = 1 THEN JSON_VALUE(JsonValue, '$.Properties.Searchable')
ELSE N''
END = N'true'
Result:
JsonPath
-----------------------------
$.Id
$.PrsonalInfo.Name.GivenName
$.PrsonalInfo.Name.FamilyName