jsonsql-serversql-server-2016sql-server-json

How to query nested fields and return with the path?


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!


Solution

  • 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