I have a following GET call response.
{
"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"
}
}
]
}
Out of the above GET response, I only need to parse the highlighted string shown below and store it into a CLOB.
href": "fa-esgu-dev5-domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001
How can I achieve this using Oracle JSON_TABLE or any other Oracle centric method?
Please advise.
You don't need JSON_TABLE but JSON_VALUE for this. As per the docs:
The SQL/JSON function JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a SQL value.
Once you know that, it's actually quite simple:
Select JSON_VALUE(json_data,'$.links[0].href'), where (1) $ is the JSON context, (2) links[0] is the first element of the "links" array and (3) href is the scalar value of the href attribute in the selected links element.
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
The "href" attribute seems to be a rest source so I'm assuming that the value you need is the string after the last forward slash. Here is how to get the only that value:
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