SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--sample data
create table data
(
  json nvarchar(max)
);

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

select * from data;


json


{ "a": 1 }

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


-- all data
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 *
from rcte r;


path
key
value
type


a
a
1
2

b
b
{ "a": 2 }
5

b.a
a
2
2


-- filter key='a'
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';


key
value
path


a
1
a

a
2
b.a