SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE JsonData
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Path] VARCHAR(MAX) NOT NULL,
    [Value] VARCHAR(MAX) NOT NULL
);
INSERT INTO JsonData
  ("Id", "Path", "Value")
VALUES
  ('1', 'FullName', '{}'),
  ('2', 'FullName.FirstName', 'Robert'),
  ('3', 'FullName.MiddleName', 'John'),
  ('4', 'FullName.LastName', 'Downey'),
  ('5', 'FullName.Suffix', 'Jr.'),
  ('6', 'CurrentDate', '{}'),
  ('7', 'CurrentDate.Month', 'February'),
  ('8', 'CurrentDate.Day', '22'),
  ('9', 'CurrentDate.Year', '2024');
9 rows affected

WITH cte AS (
    SELECT CAST(N'{}' AS nvarchar(max)) AS json, 0 AS Id

    UNION ALL

    SELECT
      IIF(jd.Path IS NULL,
        cte.json,
        IIF(ISJSON(jd.Value) = 1,
          JSON_MODIFY(cte.json, '$.' + jd.Path, JSON_QUERY(jd.value)),
          JSON_MODIFY(cte.json, '$.' + jd.Path, jd.Value)
        )
      ),
      jd.Id
    FROM cte
    OUTER APPLY (
        SELECT *
        FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY jd.Id) AS rn
            FROM JsonData jd
            WHERE jd.Id > cte.Id
        ) jd
        WHERE jd.rn = 1
    ) jd
    WHERE cte.Id IS NOT NULL
)
SELECT cte.json
FROM cte
WHERE cte.Id IS NULL;


json


{"FullName":{"FirstName":"Robert","MiddleName":"John","LastName":"Downey","Suffix":"Jr."},"CurrentDate":{"Month":"February","Day":"22","Year":"2024"}}