SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @json nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

with level_0 as (
    select
        convert(int,0) as [Level],
        convert(nvarchar(4000),N'$') as [Key],
        @json as [Value],
        convert(
            int,
            case left(@json,1)
                when N'[' then 4
                when N'{' then 5
                else 0
            end
        ) as [Type]
)
, key_value_unwrap as(
    select 
        l0.[Level] + 1 as [Level],
        convert(nvarchar(max),null) as Parent,
        l0.[Key] + iif(l0.[Type] = 5, '.' + oj.[Key], quotename(-1 + row_number() over (order by (select null)))) collate database_default as [Key],
        oj.[Value],
        oj.[Type]
    from level_0 l0
    outer apply openjson(l0.[Value]) as oj
    where l0.[Value] is not null 
    union all
    select 
        kvu.[Level] + 1 as [Level],
        convert(nvarchar(max),kvu.[Key]) as Parent,
        kvu.[Key] + iif(kvu.[Type] = 5, '.' + oj.[Key], quotename(-1 + row_number() over (order by (select null)))) as [Key],
        oj.[Value],
        oj.[Type]
    from key_value_unwrap as kvu
    outer apply openjson(kvu.[Value], 'lax $') as oj
    where kvu.[Type] in (4,5)
), _union as (
    select 
        l0.[Level],
        convert(nvarchar(max),null) as Parent,
        l0.[Key] + N'.' as [Key],
        l0.[Value],
        l0.[Type]
    from level_0 as l0
    union all
    select 
        kvu.[Level],
        kvu.Parent,
        kvu.[Key],
        kvu.[Value],
        kvu.[Type]
    from key_value_unwrap as kvu
) 
select 
    u.[Level],
    iif(u.[Level]=1,N'$.',u.Parent) as Parent,
    u.[Key],
    u.[Value],
    u.[Type]
from _union as u
where u.[Type] not in (4,5);

Level	Parent	Key	Value	Type
1	$.	$.person	Amy	1
3	$.staff[1]	$.staff[1].person	Chris	1
5	$.staff[1].staff[1]	$.staff[1].staff[1].person	Emma	1
5	$.staff[1].staff[0]	$.staff[1].staff[0].person	Dan	1
3	$.staff[0]	$.staff[0].person	Bill	1