How to parse a json with dynamic property name in OIC?

Question

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?

Answer 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.