How to achieve JSON custom format for array of values while applying Denodo nest()

Hi Denodo Team, I have created a base view with 3 employee records for Dept_id= 'D100' as below. SELECT 'D100' AS dept_id , 'E1' AS empid FROM DUAL UNION SELECT 'D100' AS dept_id , 'E2' AS empid FROM DUAL UNION SELECT 'D100' AS dept_id , 'E3' AS empid FROM DUAL **My requirement is to display the array of empid's in JSON output format (given below) through REST webservice: **"employees": {"empid": ["E1","E2","E3"] }. - I have performed nest() and here is my current output & SQL used: Query Used: select nest(empid) as employees from bv_test group by dept_id; **REST OUTPUT: ** { "name": "p_bv_test", "elements": [ { "employees": [ { "empid": "E1" }, { "empid": "E2" }, { "empid": "E3" } ] } ] } Please suggest appropriate function(s) or advise to use in Denodo to achieve the desired output. Thanks & Regards, Srujan.
user
09-01-2019 09:57:45 -0500

1 Answer

Hi, This is the expected behaviour when you publish Array values in **JSON REST API** using Virtual DataPort Server. The reason for such behaviour is that the **‘’Array’’** in Virtual DataPort Server has an associated **‘’Register’**’ type to have one or more attributes per array element. Hence, the attribute name is shown in JSON response for each array value. As you cannot directly edit the JSON response, you can apply [XSLT Transformations](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/publication_of_web_services/xslt_transformations/xslt_transformations) to the **XML REST API** in order to customize the output of the Web Services and then convert it by using **XSLT stylesheet** before sending them to the client. You can have a look at the documentation [Transforming Incoming/Outgoing Soap/Rest Messages with XSLT Stylesheets](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/appendix/transforming_incoming_outgoing_soap_rest_messages_with_xslt_stylesheets/transforming_incoming_outgoing_soap_rest_messages_with_xslt_stylesheets) for more information. Hope this helps!
Denodo Team
11-01-2019 05:21:35 -0500
You must sign in to add an answer. If you do not have an account, you can register here