How to query nested fields and return with the path?

Question

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!

Zhorov · Accepted Answer · 2020-08-05T12:49:34.987

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

How to query nested fields and return with the path?

1 Answers1