Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE ITEMS_DOWNLOADED (
  prizmid NUMBER,
  jdoc CLOB CHECK (jdoc is JSON)
);
INSERT INTO items_downloaded (prizmid, jdoc) VALUES (
  183858,
  '{
    "GTIN14": "00842768005244",
    "GUDIDDescription": "Transferrin Flex® reagent cartridge, 120 tests",
    "Packages": [
        {
            "PrizmPackageId": 4186701,
            "InnerPackageCount": "4.0000",
            "TotalDeviceCount": "4.0000",
            "PackageTypeCode": "PK",
            "PackageTypeDesc": "package",
            "InnerPrizmPackageId": 8237287,
            "PackageStatusId": 1,
            "PackageStatusDesc": "Live",
            "IsUnitOfUse": false,
            "PackageIdentifiers": [
                {
                    "ExternalPackageId": "00842768005244",
                    "ExternalPackageIdType": "GTIN",
                    "ExternalPackageIdDesignatorAgency": "GS1",
                    "ExternalPackageIdDesignator": "Primary"
                },
                {
                    "ExternalPackageId": "ABC",
                    "ExternalPackageIdType": "XYZ",
                    "ExternalPackageIdDesignatorAgency": "GS1",
                    "ExternalPackageIdDesignator": "Primary"
                }
            ],
            "PackagingCompanies": null
        }
    ],
    "PrizmId": 183858,
    "PrizmPublishDate": "2023-07-20T00:00:00",
    "PrizmRevisionDate": "2023-07-20T00:00:00"
}'
);
1 rows affected

SELECT i.prizmid,
       j.PrizmPackageId,
       j.InnerPrizmPackageId,
       CASE j.type WHEN 'GTIN' THEN j.gtin END AS gtin
FROM   items_downloaded i
       CROSS APPLY JSON_TABLE(
         i.jdoc,
         '$.Packages[*]'
         COLUMNS (
           PrizmPackageId      VARCHAR(100) PATH '$.PrizmPackageId', 
           InnerPrizmPackageId VARCHAR(100) PATH '$.InnerPrizmPackageId',
           NESTED PATH '$.PackageIdentifiers[*]' COLUMNS (
             GTIN VARCHAR(50) PATH '$.ExternalPackageId',
             Type VARCHAR(50) PATH '$.ExternalPackageIdType'
           )
         )
       ) J
WHERE  i.PRIZMID = 183858


PRIZMID
PRIZMPACKAGEID
INNERPRIZMPACKAGEID
GTIN


183858
4186701
8237287
00842768005244

183858
4186701
8237287
null
PRIZMID	PRIZMPACKAGEID	INNERPRIZMPACKAGEID	GTIN
183858	4186701	8237287	00842768005244
183858	4186701	8237287	null