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

Question

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.

Answer 1

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]