ORACLE JSON_TABLE带条件的数组的嵌套路径

ui7jx7zq 于 2023-08-08 发布在 Oracle

关注(0)|答案(1)|浏览(155)

正在运行Oracle 12c R2...
我们正在解析一些JSON数据。下面是一个简化的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"
}

字符串
因此，我想将Packages节点提取到一个扁平化的表中，包括PackageIdentifiers节点中的ExternalPackageId。这一切都工作得很好，但我想只拉一个特定的PackageIdentifier的外部 Package id，所以我试图在NESTED PATH '...' COLUMNS子句中添加它，但它对我大喊大叫。
这是我的工作查询…

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

型
它返回预期的数据：

的数据
我只想提取ExternalPackageId，虽然对于PackageIdentifier，其中ExternalPackageIdType ==“GTIN”。
所以我试着像这样把它加到查询中…

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

型
但它抱怨了NESTED PATH predicate ：

的
如何编写此查询以仅提取ExternalPackageId，其中ExternalPackageIdType =“GTIN”？
无论如何，都应该返回第一个NESTED PATH中的数据，即使第二个嵌套路径中没有GTIN记录。

JSON

来源：https://stackoverflow.com/questions/76831310/oracle-json-table-nested-path-from-array-with-condition

1条答案

按热度按时间

zour9fqk1#

您可以尝试：

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'

字符串
其中，对于样本数据：

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"
}'
);

型
产出：
| PRIZMPACKAGEID| INNERPRIZMPACKAGEID| GTIN|类型| TYPE |
| --|--|--|--| ------------ |
| 4186701| 8237287| 00842768005244| GTIN| GTIN |
Oracle 18 fiddle（我没有访问Oracle 12 r2示例的权限）
如果你总是想要行，但只想要ExternalPackageId，当ExternalPackageId等于GTIN时，你可以在SELECT子句中使用CASE表达式（而不是WHERE过滤器）：

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的

赞(0）回复(0）举报 2023-08-08

我来回答

ORACLE JSON_TABLE带条件的数组的嵌套路径

1条答案

相关问题

热门标签

最新问答