SQL Server 2019

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

create or alter function dbo.json_shred (
    @json nvarchar(max)
)
returns table 
as
return (
/*
Author:      Peter Vandivier
Date:        2021-10-04
Description: Takes an arbitrary JSON document and returns all valid
             json_paths within the document as well as the values
             present at said path and the depth within the document
             to which the given path with probe
Example:
    -- Filtering to `[Type] not in (4,5)` returns all leaf-node
    -- paths and values for the given document
    --
    declare @json nvarchar(max) = N'{"a":"foo","b":[1,2,{"d":"bar","e":[0]}],"f":{"g":"baz"},"h":null,"i":"zap"}';
    --
    select * 
    from dbo.json_shred(@json)
    where [Type] not in (4,5);
Example_Output:
    +-------+----------+-------------+-------+------+
    | Level | Parent   | Key         | Value | Type |
    +-------+----------+-------------+-------+------+
    | 1     | $.       | $.a         | foo   | 1    |
    | 1     | $.       | $.h         | NULL  | 0    |
    | 1     | $.       | $.i         | zap   | 1    |
    | 2     | $.f      | $.f.g       | baz   | 1    |
    | 2     | $.b      | $.b[0]      | 1     | 2    |
    | 2     | $.b      | $.b[1]      | 2     | 2    |
    | 3     | $.b[2]   | $.b[2].d    | bar   | 1    |
    | 4     | $.b[2].e | $.b[2].e[0] | 0     | 2    |
    +-------+----------+-------------+-------+------+
*/
    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
);
drop table if exists foo;

create table foo (
    id int not null
        constraint pk_foo primary key,
    _json nvarchar(max),
    constraint chk_is_json check(isjson(_json)=1)
);

declare @json nvarchar(max) = N'{
  "a": "foo",
  "b": [
    1,
    2,
    {
      "d": "bar",
      "e": [
        0
      ]
    }
  ],
  "f": {
    "g": "baz"
  },
  "h": null,
  "i": "zap"
}';

set @json = replace(replace(replace(@json,' ',''),nchar(10),''),nchar(13),'');

insert into foo ( id, _json )
values 
(1,@json);

set @json = N'[
  {
    "a": "foo"
  },
  {
    "f": {
      "g": "baz"
    }
  }
]';

set @json = replace(replace(replace(@json,' ',''),nchar(10),''),nchar(13),'');

insert into foo ( id, _json )
values 
(2,@json);

insert into foo ( id, _json )
values 
(3,null)
3 rows affected

select f.id, js.* 
from foo f
cross apply dbo.json_shred(f._json) js;


id
Level
Parent
Key
Value
Type


1
0
null
$.
{"a":"foo","b":[1,2,{"d":"bar","e":[0]}],"f":{"g":"baz"},"h":null,"i":"zap"}
5

1
1
$.
$.a
foo
1

1
1
$.
$.b
[1,2,{"d":"bar","e":[0]}]
4

1
1
$.
$.f
{"g":"baz"}
5

1
1
$.
$.h
null
0

1
1
$.
$.i
zap
1

1
2
$.f
$.f.g
baz
1

1
2
$.b
$.b[0]
1
2

1
2
$.b
$.b[1]
2
2

1
2
$.b
$.b[2]
{"d":"bar","e":[0]}
5

1
3
$.b[2]
$.b[2].d
bar
1

1
3
$.b[2]
$.b[2].e
[0]
4

1
4
$.b[2].e
$.b[2].e[0]
0
2

2
0
null
$.
[{"a":"foo"},{"f":{"g":"baz"}}]
4

2
1
$.
$[0]
{"a":"foo"}
5

2
1
$.
$[1]
{"f":{"g":"baz"}}
5

2
2
$[1]
$[1].f
{"g":"baz"}
5

2
3
$[1].f
$[1].f.g
baz
1

2
2
$[0]
$[0].a
foo
1

3
0
null
$.
null
0


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

select * 
from dbo.json_shred(@Employees)
where [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

id	Level	Parent	Key	Value	Type
1	0	null	$.	{"a":"foo","b":[1,2,{"d":"bar","e":[0]}],"f":{"g":"baz"},"h":null,"i":"zap"}	5
1	1	$.	$.a	foo	1
1	1	$.	$.b	[1,2,{"d":"bar","e":[0]}]	4
1	1	$.	$.f	{"g":"baz"}	5
1	1	$.	$.h	null	0
1	1	$.	$.i	zap	1
1	2	$.f	$.f.g	baz	1
1	2	$.b	$.b[0]	1	2
1	2	$.b	$.b[1]	2	2
1	2	$.b	$.b[2]	{"d":"bar","e":[0]}	5
1	3	$.b[2]	$.b[2].d	bar	1
1	3	$.b[2]	$.b[2].e	[0]	4
1	4	$.b[2].e	$.b[2].e[0]	0	2
2	0	null	$.	[{"a":"foo"},{"f":{"g":"baz"}}]	4
2	1	$.	$[0]	{"a":"foo"}	5
2	1	$.	$[1]	{"f":{"g":"baz"}}	5
2	2	$[1]	$[1].f	{"g":"baz"}	5
2	3	$[1].f	$[1].f.g	baz	1
2	2	$[0]	$[0].a	foo	1
3	0	null	$.	null	0

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