SELECT into deep structure

Question

I am relatively new to ABAP and so I still need to get used to internal tables and the like, so currently I am a little bit struggling with how to use SQL in ABAP to fill a nested structure.

For example:

TYPES: BEGIN of <<mystructure>>,
         someID type sometype,
         relatedItemsInDataModel type table of sometabletype,
       END of <<mystructure>>.
DATA wa type <<mystructure>>.

<<SELECT INTO STATEMENT>>

DATA(lv_json) = /ui2/cl_json=>serialize( data = wa compress abap_true ... ).

So basically, I've got a table (A) in the dictionary which has a one-to-many relationship to another table (B) and I want to select all items in A and for every item in A I want to select all related items in B for that record.

The reason I wanna do this is because I later on want to convert that data to JSON looking like:

[
 {
  "someID": "someValue",
  "relatedItemsInDataModel": [{...}, {...}]
 },
 {
  "someID": "someValue2",
  "relatedItemsInDataModel": [{...}, {...}, {...}, ...]
 },
 ...
]

So, am I approaching this the right way in the first place and how can I achieve what I just described?

I don't think it is possible to fill a nested structured structure with standard SQL as the SQL is relational and not hierarchical. — Jagger

Florian Florian · Accepted Answer · 2020-11-13T10:12:10

SELECTs only retrieve flat tables. Your therefore need to retrieve nested data in multiple steps and assemble it in the ABAP code.

Your example could look like this:

DATA the_ids_i_want TYPE RANGE OF sometype.

SELECT <field-list>
  FROM table_a
  INTO TABLE @DATA(selection_a)
  WHERE some_id IN @the_ids_i_want.

SELECT <field-list>
  FROM table_b
  INTO TABLE @DATA(selection_b)
  WHERE parent_id IN @the_ids_i_want.

LOOP AT selection_a INTO DATA(row_a).

  DATA(result_row) =
    VALUE <<mystructure>>(
      some_id = row_a-some_id ).

  LOOP AT selection_b INTO DATA(row_b)
      WHERE parent_id = row_a-some_id.
    
    INSERT VALUE #(
        field_a = row_b-field_a
        ... )
      INTO TABLE row_a-relatedItemsInDataModel.

  ENDLOOP.

ENDLOOP.

There are a lot of aspects that can be optimized, depending on your data. For example, the WHERE conditions may vary depending on the type of foreign key relations, you may want to consider adding sorted keys to the internal table to speed up the LOOP ... WHERE lookup, there may be shorter variants for the assembly, using FOR instead of LOOP AT, etc. - but basically, this is what you need.

SELECT into deep structure

1 Answers