Deeply Nested XML and JSON with SQL

I recently wrote about how much I like the XML generating capabilities of modern SQL, but I still struggle once in a while with creating XML (and JSON) that is nested into more than just one or two levels.

Therefore I want to share another code example about how to create XML that is deeply nested.

We’re building on the same example than last time, but our characters now also have a faction attribute and we want to group by that faction:

insert into characters values (3, 'Jar Jar Binks');

alter table characters add faction varchar2(100);

update characters set faction = 'Rebels' where id in (1,3);
update characters set faction = 'Empire' where id in (2);

commit;

create table characters (
  id integer not null primary key,
  name varchar2(100) unique,
  faction varchar2(100)
);

create table favorite_food (
  character_id integer not null references characters( id ),
  food varchar2(4000)
);

insert into characters values (1, 'Chewbacca', 'Rebels');
insert into characters values (2, 'Darth Vader', 'Empire');
insert into characters values (3, 'Jar Jar Binks', 'Rebels');

insert into favorite_food values ( 1, 'Grilled Pork');
insert into favorite_food values ( 1, 'Cooked Pork');
insert into favorite_food values ( 1, 'Raw Pork');
insert into favorite_food values ( 2, 'Cheesecake');

commit;

The result we want to get is the following XML:

<Factions>
  <Faction name="Empire">
    <Characters>
      <Character>
        <name>Darth Vader</name>
        <favouriteFoods>
          <food>Cheesecake</food>
        </favouriteFoods>
      </Character>
    </Characters>
  </Faction>
  <Faction name="Rebels">
    <Characters>
      <Character>
        <name>Jar Jar Binks</name>
        <favouriteFoods/>
      </Character>
      <Character>
        <name>Chewbacca</name>
        <favouriteFoods>
          <food>Grilled Pork</food>
          <food>Cooked Pork</food>
          <food>Raw Pork</food>
        </favouriteFoods>
      </Character>
    </Characters>
  </Faction>
</Factions>

But if we just throw in another xmlagg element, we will get an ORA-00935: Group nested too deeply:

select
  xmlserialize(
    document
    xmlelement(
      "Factions",
      xmlagg(
        xmlelement(
          "Faction",
          xmlattributes(c.faction as "name"),
          xmlelement("Characters",
            xmlagg(
              xmlelement("Character",
                xmlforest(
                  c.name as "name"
                ),
                xmlelement("favouriteFoods",
                  xmlagg(
                    xmlforest(
                      ff.food as "food"
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
    as clob indent size = 2
  )
from characters c
  left outer join favorite_food ff on c.id = ff.character_id
group by faction

This makes sense, since how should the database know what you want to group at which level?

It’s all fine as long as you group into two different levels: You have one level that goes with the group by values and one level that can be filled with the grouped values. But as soon as you want to nest deeper, you will need to use a different approach: subqueries:

select
  xmlserialize(
    document
    xmlelement(
      "Factions",
      xmlagg(
        xmlelement(
          "Faction",
          xmlattributes(c.faction as "name"),
          xmlelement("Characters",
            xmlagg(
              xmlelement("Character",
                xmlforest(
                  c.name as "name"
                ),
                xmlelement("favouriteFoods",
                  (
                    select
                      xmlagg(
                        xmlforest(
                          ff.food as "food"
                        )
                      )
                    from favorite_food ff
                    where c.id = ff.character_id
                  )
                )
              )
            )
          )
        )
      )
    )
    as clob indent size = 2
  )
from characters c
group by faction

If the subqueries get too complex, we can also use a WITH clause to structure our query a bit better into different chunks:

with character_foods as (
  select
    character_id,
    xmlagg(
      xmlforest(
        food as "food"
      )
    ) xml
  from favorite_food
  group by character_id
)
select
  xmlserialize(
    document
    xmlelement(
      "Factions",
      xmlagg(
        xmlelement(
          "Faction",
          xmlattributes(c.faction as "name"),
          xmlelement("Characters",
            xmlagg(
              xmlelement("Character",
                xmlforest(
                  c.name as "name"
                ),
                xmlelement("favouriteFoods",
                  (
                    select xml
                    from character_foods cf
                    where cf.character_id = c.id
                  )
                )
              )
            )
          )
        )
      )
    )
    as clob indent size = 2
  )
from characters c
group by faction

It’s still a bit hard to read and I encourage you to be very strict with indentation and formatting, because otherwise you might get lost quickly. But it is still an awesome and relatively easy way to get deeply nested XML structures out of your database.

But you mentioned JSON in the Title! I want JSON!

Here you go – the absolute same mechanics can be used to create a deeply nested JSON structure. As usual, it is less verbose than XML:

select
  json_serialize(
    json_arrayagg(
      json_object(
        faction,
        'characters' value json_arrayagg(
          json_object(
            name,
            'favoriteFoods' value (
              select
                json_arrayagg(
                  ff.food
                )
              from favorite_food ff
              where c.id = ff.character_id
            )
          )
        )
      )
    )
    pretty
  )
from characters c
group by faction;

This will create the following result:

[
  {
    "faction" : "Empire",
    "characters" :
    [
      {
        "name" : "Darth Vader",
        "favoriteFoods" :
        [
          "Cheesecake"
        ]
      }
    ]
  },
  {
    "faction" : "Rebels",
    "characters" :
    [
      {
        "name" : "Jar Jar Binks",
        "favoriteFoods" : null
      },
      {
        "name" : "Chewbacca",
        "favoriteFoods" :
        [
          "Grilled Pork",
          "Cooked Pork",
          "Raw Pork"
        ]
      }
    ]
  }
]

Have a lot of fun generating deepest depths JSON and XML structures!