How to populate the properties of a list of objects from SQL while saving small result sets?

Question

Description of the problem

For a report (PDF) in a Java application, I request various data from a PostgreSQL 9.6 database. The data transferred to the report also contains lists, which, in turn, contain other lists. However, in general, the report does not contain many values ​​(the number of values ​​is somewhere in the hundreds).

To query data, I use a huge SQL statement with several joins (about 8) to also query data for lists and lists in lists. But, of course, connections lead to duplication of lines in the output. For instance. following request:

select *
from (values(1,'item A'), (2, 'item B')) items(id, label)
left join (values(1, 'subItem A1'), (1, 'subItem A2'), (1, 'subItem A3')) subitems(itemId, label) on items.id=subitems.itemId
left join (values(1, 'sub2Item A1'), (1, 'sub2Item A2'), (1, 'sub2Item A3')) subitems2(itemId, label) on items.id=subitems2.itemId

returns only 10 rows for 8 values ​​that are easy to work with. However, another join in another sub-element table with three more values ​​will already include 28 rows (11 values ​​in total):

select *
from (values(1,'item A'), (2, 'item B')) items(id, label)
left join (values(1, 'subItem A1'), (1, 'subItem A2'), (1, 'subItem A3')) subitems(itemId, label) on items.id=subitems.itemId
left join (values(1, 'sub2Item A1'), (1, 'sub2Item A2'), (1, 'sub2Item A3')) subitems2(itemId, label) on items.id=subitems2.itemId
left join (values(1, 'sub3Item A1'), (1, 'sub3Item A2'), (1, 'sub3Item A3')) subitems3(itemId, label) on items.id=subitems3.itemId

and the number of rows increases rapidly with each join operation. In my example account, the query that I wrote, meanwhile, generates somewhere around half a million rows, only for a report of average size in just a couple of hundred values. When building lists from a query (I use MyBatis for this) it works fine, it is slow, consumes a lot of bandwidth and consumes a lot of memory, so this actually becomes a problem.

Ideas for a solution

• MyBatis " ", N + 1. , , , , .
• : Java, / , . , SQL, SQL .

• SQL , . JSON:

  select
    json_agg(jsonb_build_object(
          'id', items.id,
          'subA', subitems.list,
          'subB', subitems2.list,
          'subC', subitems3.list
  )) result
  from (values(1,'item A'), (2, 'item B')) items(id, label)
  left join (
    select itemId, json_agg(label) list
    from
    (values(1, 'subItem A1'), (1, 'subItem A2'), (1, 'subItem A3')) subitems(itemId, label)
    group by itemId
  ) subitems on items.id = subitems.itemId
  left join (
    select itemId, json_agg(label) list
    from
    (values(1, 'sub2Item A1'), (1, 'sub2Item A2'), (1, 'sub2Item A3')) subitems(itemId, label)
    group by itemId
  ) subitems2 on items.id = subitems2.itemId
  left join (
    select itemId, json_agg(label) list
    from
    (values(1, 'sub3Item A1'), (1, 'sub3Item A2'), (1, 'sub3Item A3')) subitems(itemId, label)
    group by itemId
  ) subitems3 on items.id = subitems3.itemId
  

  :

  [{
    "id": 1,
    "subA": ["subItem A1", "subItem A2", "subItem A3"],
    "subB": ["sub2Item A1", "sub2Item A2", "sub2Item A3"],
    "subC": ["sub3Item A1", "sub3Item A2", "sub3Item A3"]
  }, {
    "id": 2,
    "subA": null,
    "subB": null,
    "subC": null
  }]
  

  JSON - , Java . , Java, . , - json_agg jsonb_build_object, .

, , . ? - " " ? ?

Answer 1

?

, , Java-.

, .