Translate JSON TO TABLES using JSON TABLE and Nested Path

Parse each of the nested JSON structures separately not using NESTED PATH in the initial JSON_TABLE and returning the arrays using FORMAT JSON and then you can use correlated sub-queries (using CROSS APPLY or CROSS JOIN LATERAL) to parse the individual queries and cross join the rows in each of the arrays.

The part numbers can be generated using a filter expression in the JSON path (rather than rows for each property and then pivoting). The target markets can be generated and aggregated so that a single row is always generated.

Like this:

SELECT jt.product_key,
       jt.group_subtype_id,
       jt.group_subtype_name,
       jt.variant_id,
       jt.variant_name,
       jt.rb_customer_id,
       pn.partnumber,
       pn.pn_type,
       pn.mat_status,
       pn.property_id_4_name || ':' || pn.property_id_4_value AS property_id_4,
       pn.property_id_5_name || ':' || pn.property_id_5_value AS property_id_5,
       pn.property_id_6_name || ':' || pn.property_id_6_value AS property_id_6,
       pn.property_id_7_name || ':' || pn.property_id_7_value AS property_id_7,
       pn.property_id_8_name || ':' || pn.property_id_8_value AS property_id_8,
       dn.document_number,
       dn.document_version,
       dn.document_type,
       tm.country_name,
       tm.iso_code,
       hi.child_product
FROM   (
         SELECT jt.*
         FROM   table_name t
                CROSS APPLY JSON_TABLE(
                  t.value,
                  '$'
                  COLUMNS (
                    product_key           NUMBER        PATH '$.general.product_key',
                    group_subtype_id      NUMBER        PATH '$.general.group_subtype_id',
                    group_subtype_name    VARCHAR2(100) PATH '$.general.group_subtype_name',
                    variant_id            NUMBER        PATH '$.general.variant_id',
                    variant_name          VARCHAR2(100) PATH '$.general.variant_name',
                    rb_customer_id        NUMBER        PATH '$.general.rb_customer_id',
                    partnumbers           CLOB FORMAT JSON PATH '$.partnumbers',
                    document_numbers      CLOB FORMAT JSON PATH '$.document_numbers',
                    target_markets        CLOB FORMAT JSON PATH '$.target_markets',
                    hierarchy_information CLOB FORMAT JSON PATH '$.hierarchy_information'
                  )
                ) jt
         WHERE  ROWNUM > 0
       ) jt
       CROSS APPLY JSON_TABLE(
         jt.partnumbers,
         '$[*]'
         COLUMNS (
           partnumber          VARCHAR2(100) PATH '$.partnumber',
           pn_type             VARCHAR2(100) PATH '$.pn_type',
           mat_status          VARCHAR2(100) PATH '$.mat_status',
           property_id_4_name  VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="4").property_name',
           property_id_4_value VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="4").value',
           property_id_5_name  VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="5").property_name',
           property_id_5_value VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="5").value',
           property_id_6_name  VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="6").property_name',
           property_id_6_value VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="6").value',
           property_id_7_name  VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="7").property_name',
           property_id_7_value VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="7").value',
           property_id_8_name  VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="8").property_name',
           property_id_8_value VARCHAR2(100) PATH '$.properties[*]?(@.property_id=="8").value'
         )
       ) pn
       CROSS APPLY JSON_TABLE(
         jt.document_numbers,
         '$[*]' COLUMNS (
             document_number  VARCHAR2(100) PATH '$.document_number',
             document_version VARCHAR2(100) PATH '$.document_version',
             document_type    VARCHAR2(100) PATH '$.document_type'
           )
       ) dn
       CROSS JOIN LATERAL (
         SELECT LISTAGG(country_name, ':') AS country_name,
                LISTAGG(iso_code, ':') AS iso_code
         FROM   JSON_TABLE(
                  jt.target_markets,
                  '$[*]'
                  COLUMNS (
                    country_name VARCHAR2(100) PATH '$.country_name',
                    iso_code     VARCHAR2(3)   PATH '$.iso_code'
                  )
                )
       ) tm
       OUTER APPLY JSON_TABLE(
         jt.hierarchy_information,
         '$[*]' COLUMNS (
             child_product    VARCHAR2(100) PATH '$'
           )
       ) hi

Note: WHERE ROWNUM > 0 looks like it does nothing as it will always be true but it is used to materialise the initial query which allows you to chain the correlated JSON_TABLEs.

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT EMPTY_CLOB() || '{
    "general": {
      "product_key": "501088",
      "group_subtype_id": 1,
      "group_subtype_name": "Wheel Speed Sensor",
      "variant_id": 6,
      "variant_name": "DF22",
      "rb_customer_id": 287383
    },
    "partnumbers": [
      {
        "partnumber": "F04FD009BD",
        "pn_type": "Series OEM",
        "mat_status": "00 - planned",
        "properties": [
          {"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"},
          {"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"},
          {"property_id":6,"property_name":"Axle","value_id":62,"value":"Front / Rear Right"},
          {"property_id":7,"property_name":"Base Type - Development P/N","value_id":72,"value":"FFF"},
          {"property_id":8,"property_name":"Base Type - Released P/N","value_id":73,"value":"SSS"}
        ]
      }
    ],
    "document_numbers": [
      {"document_number":"1234569871","document_version":"05","document_type":"TCD"},
      {"document_number":"0123456789","document_version":"01","document_type":"TCD"},
      {"document_number":"1234569870","document_version":"05","document_type":"TCD"},
      {"document_number":"1234567890","document_version":"01","document_type":"TCD"}
    ],
    "target_markets": [
      {"country_name":"Belize","iso_code":"BZ"},
      {"country_name":"Central African Republic","iso_code":"CF"},
      {"country_name":"Albania","iso_code":"AL"}
    ]
  }'
  FROM DUAL;

Outputs:

fiddle