ORACLE JSON_TABLE NESTED PATH from Array with Condition

Running Oracle 12c R2...

We're parsing out some JSON data. Here is a pared-down example of the JSON...

{
    "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"
                }
            ],
            "PackagingCompanies": null
        }
    ],
    "PrizmId": 183858,
    "PrizmPublishDate": "2023-07-20T00:00:00",
    "PrizmRevisionDate": "2023-07-20T00:00:00"
}

So I want to pull out the Packages nodes into a flattened table, including the ExternalPackageId from the PackageIdentifiers nodes. This all works well, but I want to ONLY pull the external packaged id for a specific PackageIdentifier, so I'm trying to add that in the NESTED PATH '...' COLUMNS clause but it yells at me.

This is my working query...

SELECT I.PRIZMID, J.*
FROM fdbprizm.ITEMS_DOWNLOADED I, 
JSON_TABLE(I.JDOC, '$' COLUMNS 
    NESTED PATH '$.Packages[*]' COLUMNS (
           PrizmPackageId VARCHAR(100) PATH '$.PrizmPackageId', 
           InnerPrizmPackageId VARCHAR(100) PATH '$.InnerPrizmPackageId',
           NESTED PATH '$.PackageIdentifiers[*]' COLUMNS (
           GTIN VARCHAR(50) PATH '$.ExternalPackageId'
                  
           )
    )
) J
WHERE PRIZMID = 183858

which returns the expected data:

I only want to pull the ExternalPackageId though for the PackageIdentifier where ExternalPackageIdType == "GTIN".

So I tried adding that to the query like so...

SELECT I.PRIZMID, J.*
FROM fdbprizm.ITEMS_DOWNLOADED I, 
JSON_TABLE(I.JDOC, '$' COLUMNS 
    NESTED PATH '$.Packages[*]' COLUMNS (
           PrizmPackageId VARCHAR(100) PATH '$.PrizmPackageId', 
           InnerPrizmPackageId VARCHAR(100) PATH '$.InnerPrizmPackageId',
           NESTED PATH '$.PackageIdentifiers[0]?(@.ExternalPackageIdType == "GTIN")' COLUMNS (
           GTIN VARCHAR(50) PATH '$.ExternalPackageId'
                  
           )
    )
) J
WHERE PRIZMID = 183858

But it complains about the NESTED PATH predicate:

How can I write this query to only pull the ExternalPackageId where ExternalPackageIdType = "GTIN" ?

The data from the first NESTED PATH should be returned regardless, even if there is no GTIN record in the second nested path.

You can try:

SELECT i.prizmid,
       j.*
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
AND    j.TYPE = 'GTIN'

Which, for the sample data:

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"
                }
            ],
            "PackagingCompanies": null
        }
    ],
    "PrizmId": 183858,
    "PrizmPublishDate": "2023-07-20T00:00:00",
    "PrizmRevisionDate": "2023-07-20T00:00:00"
}'
);

Outputs:

Oracle 18 fiddle (I don't have access to an Oracle 12r2 instance)

If you always want the row but only want the ExternalPackageId when the ExternalPackageId equals GTIN then you can use a CASE expression in the SELECT clause (rather than a WHERE filter):

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

Oracle 18 fiddle