1

I need to consume and parse incoming json from a third party system in my code. I used RestTemplate to do it. So the response from the system looks like below. { "data": { "05AAAFW9419M11Q": { "gstin": "05AAAFW9419M11Q", "error_cd": "SWEB_9035", "message": "Invalid GSTIN / UID" } } }

Now the problem is the property name ("05AAAFW9419M11Q" in this case) in dynamic and in the next response it would be another string. In this case, how can I parse this json as this is not fixed in Oracle Integration Cloud? Response wrapper is not capturing the data apart from the one that is used for configuring the adapter which is fair enough as fieldname itself is changing. Is there is any workaround for this?

simanta bordoloi
  • 11
  • 1

1 Answers1

-1

You will have to go to PL/SQL and dynamic SQL, and if it's always the value of gstin entry, you can get the path of the key with

select '$.data.' ||
    json_query(js_column, '$.data.*.gstin') into v_key path from table_with_json_column where ... conditions... ;

(assuming there is only 1 "data" per JSON payload) to later build a dynamic query based on json_table.

p3consulting
  • 2,721
  • 2
  • 12
  • 10
  • If you grab the value from `gstin` property then there's no reason to build a dynamic SQL, because your path expression already accesses the content of an object. If the property name is not the same, then [`JSON_DATAGUIDE`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_DATAGUIDE.html) is more reliable way – astentx Jan 31 '23 at 11:17
  • https://dbfiddle.uk/bvTY5YHg – astentx Jan 31 '23 at 11:23
  • I don't understand how this could solve the problem in the original question. In Oracle Integration Cloud unfortunately the json received from a third party api, for example, does not contain data that has not been declared in the JSON example provided to the adapter. And in case of JSON with dynamic property names the schema generated would contain only the property included in the example. I'm stuck in the same situation unfortunately and I cannot find a solution. – lincetto Mar 01 '23 at 13:41