SQL Server 2019

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

--Template definition
create table person ( 
  Id int primary key,
  name nvarchar(20)
);
insert person 
values
(1,'Josian'),
(2,'Paul'),
(3,'Seasonique');

create table LuckyNumbers (PersonId int foreign key references person(Id),
LuckyNumber int);

insert LuckyNumbers (PersonId, LuckyNumber)
values 
(1,581),
(1,777),
(2,123551),
(2,5),
(2,646464),
(2,1345),
(2,75),
(2,76),
(3,1);
12 rows affected

--Query to JSON
select p.id,p.name,Json_Query('['+string_agg(ln.LuckyNumber,',')+']') 'LuckyNumbers'
from person p
inner join LuckyNumbers ln on p.id=ln.PersonId
group by p.id,p.name
for json path


JSON_F52E2B61-18A1-11d1-B105-00805F49916B


[{"id":1,"name":"Josian","LuckyNumbers":[581,777]},{"id":2,"name":"Paul","LuckyNumbers":[123551,5,646464,1345,75,76]},{"id":3,"name":"Seasonique","LuckyNumbers":[1]}]


--JSON to Query
declare @JsonOutput nvarchar(max)=(
select p.id,p.name,Json_Query('['+string_agg(ln.LuckyNumber,',')+']') 'LuckyNumbers'
from person p
inner join LuckyNumbers ln on p.id=ln.PersonId
group by p.id,p.name
for json path);

select id,name,luckynumber from openjson(@JsonOutput)
  WITH (
    id INT 'strict $.id',
    name NVARCHAR(50) '$.name',
    LuckyNumbers NVARCHAR(MAX) '$.LuckyNumbers' AS JSON
)
OUTER APPLY OPENJSON(LuckyNumbers)
  WITH (LuckyNumber NVARCHAR(8) '$');


id
name
luckynumber


1
Josian
581

1
Josian
777

2
Paul
123551

2
Paul
5

2
Paul
646464

2
Paul
1345

2
Paul
75

2
Paul
76

3
Seasonique
1

id	name	luckynumber
1	Josian	581
1	Josian	777
2	Paul	123551
2	Paul	5
2	Paul	646464
2	Paul	1345
2	Paul	75
2	Paul	76
3	Seasonique	1