使用ORACLE JSON_TABLE解析复杂JSON GET CALL

我有一个下面的GET调用响应。

{
  "PrepaymentIncludedonInvoiceFlag": null,
  "ReferenceKeyTwo": null,
  "ProductTable": null,
  "links": [
    {
      "rel": "self",
      "href": "domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001",
      "name": "invoiceLines",
      "kind": "item",
      "properties": {
        "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000278"
      }
    }
  ]
}

在上面的GET响应中，我只需要解析下面突出显示的字符串并将其存储到CLOB中。
href”：“fa-esgu-dev5-domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001
如何使用Oracle JSON_TABLE或任何其他以Oracle为中心的方法来实现这一点？
请告知。

你不需要JSON_TABLE，而需要JSON_VALUE。关于docs：

SQL/JSON函数JSON_VALUE在JSON数据中查找指定的标量JSON值，并将其作为SQL值返回。*

一旦你知道了这一点，其实很简单：
选择JSON_VALUE(json_data,'$.links[0].href')，其中（1）$是JSON上下文，（2）links[0]是“links”数组的第一个元素，（3）href是所选links元素中href属性的标量值。

with json_doc AS 
(SELECT
'{
  "PrepaymentIncludedonInvoiceFlag": null,
  "ReferenceKeyTwo": null,
  "ProductTable": null,
  "links": [
    {
      "rel": "self",
      "href": "domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001",
      "name": "invoiceLines",
      "kind": "item",
      "properties": {
        "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000278"
      }
    }
  ]
}' AS json_data FROM dual
)
SELECT 
  JSON_VALUE(json_data,'$.links[0].href')
from json_doc WHERE json_data IS JSON;

JSON_VALUE(JSON_DATA,'$.LINKS[0].HREF')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------
domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001

“href”属性似乎是一个rest源，所以我假设您需要的值是最后一个正斜杠之后的字符串。以下是如何获得唯一的值：

with json_doc AS 
(SELECT
'{
  "PrepaymentIncludedonInvoiceFlag": null,
  "ReferenceKeyTwo": null,
  "ProductTable": null,
  "links": [
    {
      "rel": "self",
      "href": "domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001",
      "name": "invoiceLines",
      "kind": "item",
      "properties": {
        "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000278"
      }
    }
  ]
}' AS json_data FROM dual
)
SELECT 
  SUBSTR(JSON_VALUE(json_data,'$.links[0].href'),INSTR(JSON_VALUE(json_data,'$.links[0].href'),'/',-1) + 1) as val
from json_doc WHERE json_data IS JSON;

VAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
--------------------------------------------------------------------
00020000000EACED00057708000110D93B462A400000000AACED0005770400000001

使用ORACLE JSON_TABLE解析复杂JSON GET CALL

1条答案

相关问题

热门标签

最新问答