Is it possible to get all the paths of json strings stored in an SQL column-CodePudding

Say I have a JSON data stored in a varchar(max) column in a database. Is it possible to use SQL to get all the JSON paths present in that data. For example for the following JSON:

{
    "dog":
    { 
        "name":"Rover",
        "age": 6,
        "likes:["catch", "eating"]
    }
}

I would get the output of:

$.
$.dog
$.dog.name
$.dog.age
$.dog.likes[0]
$.dog.likes[1]

I have looked at functions including json_query and json_value but they seem to be more about getting data from the JSON rather than the metadata I require.

I am using SQL Server 2018.

CodePudding user response：

Try a recursive CTE

DECLARE @s varchar(max) = '{
    "dog":
    { 
        "name":"Rover",
        "age": 6,
        "likes":["catch", "eating"]
    }
}';

with cte as (
  select  [type],  '$'   case when roottype = 4 then '[' [key] ']' else '.' [key] end  as path
  from (
     select r.[type] , dummy.[type] roottype, r.[key]
     from  OPENJSON('{"dummy":'   @s  '}', '$') dummy
     cross apply OPENJSON(@s, '$') r
     ) t
   
   union all 
   
   select j.[type],  path   case when cte.[type] = 4 then '[' j.[key] ']' else '.' j.[key] end 
   from cte 
   cross apply OPENJSON(@s, path) j
   where cte.[type] >= 4 
 )
 select * 
 from cte;

Returns

type    path
5   $.dog
1   $.dog.name
2   $.dog.age
4   $.dog.likes
1   $.dog.likes[0]
1   $.dog.likes[1]