JSON Hierarchy Problems

Question

I don't have a recursive relationship here, but a problem of several child tables having the same parent, and having multiple records in each child table. Here's an example:

   create table #Schedules (
        ScheduleId int,
        ScheduleName varchar(20)
        );

    create table #ScheduleThings (
        ScheduleThingId int,
        ScheduleId int,
        Thing decimal(18,2));

    create table #ScheduleOtherThings (
        ScheduleOtherThingId int,
        ScheduleId int,
        OtherThing varchar(50));

I insert some typical sample data:

insert into #Schedules (
        ScheduleId,
        ScheduleName )
    values 
        (1, 'A'),
        (2, 'B');

insert into #ScheduleThings (
    ScheduleThingId,
    ScheduleId,
    Thing )
values
   (1, 1, 10.22),
   (2, 1, 11.02),
   (3, 1, 11.89),
   (4, 2, 19.23),
   (5, 2, 20.04),
   (6, 2, 20.76),
   (7, 2, 21.37);

    insert into #ScheduleOtherThings (
        ScheduleOtherThingId,
        ScheduleId,
        OtherThing )
    values
        (1, 1, 'Always'),
        (2, 1, 'Sometimes'),
        (3, 2, 'Seldom'),
        (4, 2, 'Always'),
        (5, 2, 'Never');

    declare @results table (result xml);

I've then tried 2 similar approaches (3 or 4 actually), but here is one:

insert into @Results (
        result )
    select fr.result from (
    select
        s.ScheduleId             as [schedules.schedule_id],
        s.ScheduleName           as [schedules.schedule_name],
        st.ScheduleThingId       as [schedules.schedule_things.schedule_thing_id],
        st.Thing                 as [schedules.schedule_things.thing],
        sot.ScheduleOtherThingId as [schedules.schedule_other_things.schedule_other_thing_id],
        sot.OtherThing           as [schedules.schedule_other_things.other_thing]
      from #Schedules s
      join #ScheduleThings st
        on st.ScheduleId = s.ScheduleId
      join #ScheduleOtherThings sot
        on sot.ScheduleId = s.ScheduleId
     where s.ScheduleId = 1
       and st.ScheduleThingId < 3
      for json path, root('schedules') ) fr(result) ;

    select * from @Results;

This attempt gives me:

{
"schedules": [
    {
        "schedules": {
            "schedule_id": 1,
            "schedule_name": "A",
            "schedule_things": {
                "schedule_thing_id": 1,
                "thing": 10.22
            },
            "schedule_other_things": {
                "schedule_other_thing_id": 1,
                "other_thing": "Always"
            }
        }
    },
    {
        "schedules": {
            "schedule_id": 1,
            "schedule_name": "A",
            "schedule_things": {
                "schedule_thing_id": 1,
                "thing": 10.22
            },
            "schedule_other_things": {
                "schedule_other_thing_id": 2,
                "other_thing": "Sometimes"
            }
        }
    },

and removing 'schedules' from the dot notation entirely has no significant impact:

{
    "schedules": [
        {
            "schedule_id": 1,
            "schedule_name": "A",
            "schedule_things": {
                "schedule_thing_id": 1,
                "thing": 10.22
            },
            "schedule_other_things": {
                "schedule_other_thing_id": 1,
                "other_thing": "Always"
            }
        },
        {
            "schedule_id": 1,
            "schedule_name": "A",
            "schedule_things": {
                "schedule_thing_id": 1,
                "thing": 10.22
            },
            "schedule_other_things": {
                "schedule_other_thing_id": 2,
                "other_thing": "Sometimes"
            }
        },

What I need (and what I think is the proper JSON structure) is like:

{
    "schedules": [
        {
            "schedule_id": 1,
            "schedule_name": "A",
            "schedule_things": [
                {
                    "schedule_thing_id": 1,
                    "thing": 10.22
                },
                {
                    "schedule_thing_id": 2,
                    "thing": 11.02
                },
            ]           
            "schedule_other_things": [
                {
                    "schedule_other_thing_id": 1,
                    "other_thing": "Always"
                },
                {
                    "schedule_other_thing_id": 2,
                    "other_thing": "Sometimes"
                }

            ]   
        }           

    ]
}

In other words, the attributes of the parent 'Schedule' record appear one time, an object of ScheduleThings follows, including all child ScheduleThings, followed by an object of ScheduleOtherThings, etc.

I don't understand yet why my dot specifications don't make it clear which attributes belong to the root object, and therefore that I don't need those attributes repeated. But, I especially don't understand why the entire dataset is flattened--even when I think I've used the dot notation to make the parent-child relationships very explicit.

Answer 1

You could try nesting the calls to for json

Such as...

select
  fr.result
from
(
  select
    s.ScheduleId             as [schedules.schedule_id],
    s.ScheduleName           as [schedules.schedule_name],
    (
      SELECT ScheduleThingId, Thing
        FROM #ScheduleThings
       WHERE ScheduleId = s.ScheduleId
         AND ScheduleThingId < 3
         FOR JSON PATH
    )
      AS [schedules.schedule_things],
    (
      SELECT ScheduleOtherThingId, OtherThing
        FROM #ScheduleOtherThings
       WHERE ScheduleId = s.ScheduleId
         FOR JSON PATH
    )
      AS [schedules.schedule_other_things]
  from
    #Schedules s
  where
    s.ScheduleId = 1
  for json path, root('schedules') 
)
  fr(result) ;

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e9a9c55b2daaac4e0f48d52a87bfede9