CODEWITHSUNDEEP
oracle-apex
ckp7blessed
ckp7blessed

Reputation: 135

Oracle Apex 22.21 - REST data source - nested JSON array - discovery

I need to get APEX Rest Data Source to parse my JSON which has a nested array. I've read that JSON nested arrays are not supported but there must be a way.

I have a REST API that returns data via JSON as per below. On Apex, I've created a REST data source following the tutorial on this Oracle blog link

However, Auto-Discovery does not 'discover' the nested array. It only returns the root level data.

[ {
  "order_number": "so1223",
  "order_date": "2022-07-01",
  "full_name": "Carny Coulter",
  "email": "[email protected]",
  "credit_card": "3545556133694494",
  "city": "Myhiya",
  "state": "CA",
  "zip_code": "12345",
  "lines": [
    {
      "product": "Beans - Fava, Canned",
      "quantity": 1,
      "price": 1.99
    },
    {
      "product": "Edible Flower - Mixed",
      "quantity": 1,
      "price": 1.50
    }
  ]
},
{
  "order_number": "so2244",
  "order_date": "2022-12-28",
  "full_name": "Liam Shawcross",
  "email": "[email protected]",
  "credit_card": "6331104669953298",
  "city": "Humaitá",
  "state": "NY",
  "zip_code": "98670",
  "lines": [
    {
      "order_id": 5,
      "product": "Beans - Green",
      "quantity": 2,
      "price": 4.33
    },
    {
      "order_id": 1,
      "product": "Grapefruit - Pink",
      "quantity": 5,
      "price": 5.00
    }
  ]
},
]

So in the JSON above, it only 'discovers' order_numbers up to zip_code. The 'lines' array with attributes order_id, product, quantity, & price do not get 'discovered'.

I found this SO question in which Carsten instructs to create the Rest Data Source manually. I've tried changing the Row Selector to "." (a dot) and leaving it blank. That still returns the root level data.

Row Selector

Changing the Row Selector to 'lines' returns only 1 array for each 'lines'

So in the JSON example above, it would only 'discover':

    {
      "product": "Beans - Fava, Canned",
      "quantity": 1,
      "price": 1.99
    }
    {
      "order_id": 5,
      "product": "Beans - Green",
      "quantity": 2,
      "price": 4.33
    }

and not the complete array..

This is how the Data Profile is set up when creating Data Source manually.

There's another SO question with a similar situation so I followed some steps such as selecting the data type for 'lines' as JSON Document. I feel I've tried almost every selector & data type. But obviously not enough.

The docs are not very helpful on this subject and it's been difficult finding links on Google, Oracle Blogs, or SO.

My end goal would be to have two tables as below auto synchronizing from the API.

orders
    id pk
    order_number num
    order_date date
    full_name vc(200)
    email vc(200)
    credit_card num
    city vc(200)
    state vc(200)
    zip_code num

    lines
        order_id /fk orders
        product vc(200)
        quantity num
        price num

view orders_view orders lines

Upvotes: 1

Views: 1791

Answers (2)

Steve Muench
Steve Muench

Reputation: 11

APEX 24.1 now supports this. Check out this blog article by my colleague Carsten Czarski to learn more:

APEX 24.1: REST Data Sources and nested JSON responses

Upvotes: 1

Carsten
Carsten

Reputation: 1114

As you're correctly stating, REST Data Sources do not support nested arrays - a REST Source can only "extract" one flat table from the JSON response. In your example, the JSON as such is an array ("orders"). The Row Selector in the Data Profile would thus be "." (to select the "root node").

That gives you all the order attributes, but discovery would skip the lines array. However, you can manually add a column to the Data Profile, of the JSON Document data type, and using lines as the selector.

As a result, you'd still get a flat table from the REST Data Source, but that table contains a LINES column, which contains the "JSON Fragment" for the order line items. You could then synchronize the REST Source to a local table ("REST Synchronization"), then you can use some custom code to extract the JSON fragments to a ORDER_LINES child table.

Does that help?

Upvotes: 1

Related Questions