ORACLE JSON_TABLE NESTED PATH from Array with Condition

Question

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.

Answer 1

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:

PRIZMID	PRIZMPACKAGEID	INNERPRIZMPACKAGEID	GTIN	TYPE
183858	4186701	8237287	00842768005244	GTIN

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