Skip to Main Content
Feature Request FR-2421
Product Area Application Builder
Status DELIVERED

5 Voters

Allow me to use real JSON paths in REST Service Row Selector

anton nielsen Public · Apr 7 2022

Idea Summary
When creating a REST Data Source I am required to enter a “Row Selector.” This value, however, is not treated as the final row selector--the APEX engine puts double quotes around what I enter when it creates its JSON_TABLE statement. Hence

result.*

becomes

“result”."*"

in the json_table statement. 

This makes it impossible to have the declarative REST data source work.

Use Case
The JSON below is an example.

{
 "header": {
   "type": "esummary",
   "version": "0.3"
 },
 "result": {
   "35189561": {
     "uid": "35189561",
     "pubdate": "2022 Mar 15",
     "epubdate": "2022 Feb 15",
     "lastauthor": "Gomika Udugamasooriya D",
     "title": "Unbiased peptoid cell screen identifies a peptoid targeting newly appeared cell surface vimentin on tumor transformed early lung cancer cells."
   },
   "35288734": {
     "uid": "35288734",
     "pubdate": "2022 Mar 15",
     "epubdate": "",
     "lastauthor": "Minna JD",
     "title": "Lung Cancer Cell of Origin: Controversy and Clinical Translational Implications."
 }
}

…

Preferred Solution (Optional)
Stop putting double quotes around the row selector.

This has been delivered in Oracle APEX 23.1!

Comments

Comments

  • carsten.czarski APEX Team OP 3.2 years ago

    Hi Anton,

    With the upcoming release 22.1, we plan to fix bug 33438771 ("improve auto-enquoting and escaping of JSON Path Expressions and avoid over-escaping filter predicates"). That could already resolve such issues.

    But when I look at your example, I see that you're trying result.* as the row selector. As your JSON example does not contain an array, can you better describe your use-case here? Which parts of your JSON document should be the “table” and which attributes should be the “columns” …?

    regards

    -Carsten

  • anton nielsen OP 3.2 years ago

    Hi Carsten,

    Thanks…and you are correct that I don't  have an array in this case. I updated the description to include a little more info (I trimmed it to just give enough to get an idea). I looked at the APEX trace of how APEX constructs the json_table. I suspect even with the bug fix it may not work because it always adds [*]. In my case, I don't want the following query

    select *
        from json_table(
            l_clob, '$.result.*'
            columns (
                pm_uid                  varchar2(2000)      path '$.uid',
                pubdate                 varchar2(2000)      path '$.pubdate',
                epubdate                varchar2(2000)      path '$.epubdate',
                lastauthor              varchar2(2000)      path '$.lastauthor',
                pm_title                varchar2(2000)      path '$.title'
                )
            )

    There is no array, but it gives the results I want.

    Thanks!!

    Anton

  • carsten.czarski APEX Team OP 3.1 years ago

    Hi Anton,

    sorry for the late response; I understand the requirement now; and you're right: The bug fix won't help.

    This case would be new to data profiles: the expected result is an array of rows, while the JSON does actually not contain an array. This requires some  thinking …

    regards

    -Carsten