You can translate the question and the replies:

Fill JSON with arrays for restful API POST Method

Hi, I need to implement a POST on restful API with JSON structure and compound arrays and registers. I can create the data from other views as flattened table, but now I need to create the JSON structure for it to execute a POST (INSERT). Structure is like this: ``` { "KEY": "AAA", "YEAR": "2022", "TABLE_1": [ { "id": 1, "type": 17, "quantity": 10.0, "SUB_TABLE_1": [ { "id": 1, "type": 1, "quantity": 100.0 }, { "id": 2, "type": 5, "quantity": 200.0 } ] }, { "id": 2, "type": 17, "quantity": 5.0, "SUB_TABLE_1": [ { "id": 1, "type": 1, "quantity": 200.0 }, { "id": 2, "type": 3, "quantity": 10.0 } ] } ] } ``` How can I create call the POST API and fill the JSON structure with my flattened data?
user
29-06-2023 09:48:50 -0400
code

3 Answers

Hi, As per my understanding, you would like to do a POST request to your view using a JSON input and through Denodo’s RESTful Web Service. Additionally, you would also like to create a JSON document from your views with compound values like registers and arrays. Denodo’s RESTful Web Service supports POST requests using a JSON document to send data. To do this, you will have to set the URL that represents the view, the HTTP headers and the body of the request where you can specify the data to send in JSON format. You can refer to this documentation [RESTful Web Service / IDU Requests](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/restful_architecture/restful_web_service/idu_requests) for more details on how to implement this. An important detail to note, to execute Insert/Delete/Update operations over a view, you have to make sure that your view is “updateable”. For example, only a view created over JDBC, ODBC or Salesforce data sources may be updated. Additionally, Virtual DataPort server decides if a view is updateable or not by following the rules of the SQL 92 standard. You can refer to the documentation [Inserts, Updates and Deletes Over Views](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/inserts_updates_and_deletes_over_views/inserts_updates_and_deletes_over_views#inserts-updates-and-deletes-over-views) to know more which views can be updated. I would suggest confirming the details of the view you want to execute POST requests against these limitations. If you would like to create your JSON document from your view that has compound values like registers and arrays, you can also use Denodo’s RESTful Web Service. When executing the GET request to your view, you can add the `Accept` header `application/json` to the HTTP request or add the `$format=JSON` parameter to the URL. This will give you data in the JSON structure as you have specified in your example. You can learn more about this in the documentation [RESTful Web Service / JSON Representation](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/restful_architecture/restful_web_service/representations#json-representation). Hope this helps!
Denodo Team
30-06-2023 04:04:58 -0400
code
Hi, thanks for your prompt response! I think this is a misunderstanding. I do not try to create the API in DENODO, but call the restAPI in an external system. I have created JSON DataSources for GET and PUT Methods and they work, when I write the data into the body. Now I need to join my base view of the DataSource for PUT with my flattened data from DENODO Views, I cannot link to the compound datatypes like arrays in that base view. I can only map into simple columns to the base view of API - not into arrays. Hope this describes my problem clearer.
user
30-06-2023 05:48:20 -0400
Hi, I tried to reproduce the scenario you described, and I was able to join the views using a column that is inside an array to a simple column from another view. Though it is not possible to do it via the graphical interface/drag-and-drop functionally in the Model tab, you can configure it using the Join Conditions tab, and clicking the “Add new condition” button. From here you can select a column from the array to use in the join condition. You can refer to the steps mentioned in the documentation [Creating Join Views](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_derived_views/creating_join_views/creating_join_views). If you encounter any error when configuring the join condition, I would suggest checking the logs located in `<DENODO_HOME>\logs\vdp\vdp.log` to get more information on the issue. If you have a valid support user, I suggest opening a support ticket if you still encounter any issues. Hope this helps!
Denodo Team
03-07-2023 19:46:00 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here