TSQL: Recursive Descent in JSON Path

Question

I was hoping to take advantage of $.. in json_value() function within TSQL queries using stored json documents. Unfortunately it doesn't work:

JSON path is not properly formatted. Unexpected character '.' is found at position 2.

and according to documentation, there was even no intention to:

Path steps can contain the following elements and operators.

Key names. For example, $.name and $."first name". If the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes.

Array elements. For example, $.product[3]. Arrays are zero-based.

The dot operator (.) indicates a member of an object. For example, in $.people1.surname, surname is a child of people.

Is there any other method, how to find the attribute a at any level(s) in structured json stored in a TSQL table column?

For example, let's have a on arbitrary level in the json document:

select json_value(json, '$..a') from data would return both values (thus 1, 2) for following values in data.json column:

first:

{
    "a": 1
}

second:

{
    "b": {
        "a": 2
    }
}

Answer 1

SQL Server has indeed limited support for JSON expressions.

You could mix a recursive common table expression with the openjson() function.

Sample data

create table data
(
  json nvarchar(max)
);

insert into data (json) values
('{ "a": 1 }'),
('{ "b": { "a": 2 } }');

Solution

with rcte as
(
  select x.[key] as path,
         x.[key],
         x.[value],
         x.[type]
  from data d
  cross apply openjson(d.json, '$') x
    union all
  select r.[path] + '.' + rr.[key],
         rr.[key],
         rr.[value],
         rr.[type]
  from rcte r
  cross apply openjson(r.[value]) rr
  where r.[type] = 5 -- 5=object
)
select r.[key],
       r.[value],
       r.[path]
from rcte r
where r.[key] = 'a';

Result

key value path
--- ----- ----
a   1     a
a   2     b.a

Fiddle (with intermediate recursive common table expression results).