You can translate the question and the replies:

Multiple Nest()

Hello I am attempting to create multiple nests in my json output within denodo. I have the following vql statement in the derived view; ``` CREATE OR REPLACE VIEW dv_view2_name FOLDER = '/03 - business entities/project/03 - derived views' AS SELECT first_value AS first_value, second_value AS second_value, nest( third_value, nest( fourth_value, fifth_value, sixth_value ) AS second_data, seventh_value, nest( eighth_value, ninth_value, tenth_value ) AS third_data, eleventh_value ) AS first_data FROM dv_view1_name GROUP BY first_value, second_value; ALTER VIEW dv_view2_name LAYOUT ( dv_view1_name = [20, 20, 271, 767] ); ``` I am getting the following error Cannot save the view com.denodo.vdb.admin.model.vdbserverproxy.VDBServerProxyException: Syntax error: Exception parsing query near 'AS' Is it possible to do multiple nest() within nest() in the vql so the RESTapi json output reflects the desired structure? Or is there a better/ different way to create more complex json output.
User
27-10-2020 19:36:23 -0400

1 Answer

Hi, For accomplishing your requirement, I would use sub queries. Ex:- Assume we have a view which has product details (name and description) and product storage details (city, address). select productid, **Nest**(product_dtl, storage_dtl,country) as Product_storage_Dtl from ( select **Nest**(productname,description)as product_dtl , **Nest**(city,address) as storage_dtl, productid, country from dv_products group by productid,country ) group by productid,country The sub query creates the lower level of Nesting for product detail (product_dtl) and storage detail (storage_dtl). The outer query creates the top level Nesting on the product and storage details which are nested in the sub query and additionally the country column as well. Syntax for using a Nest function inside another Nest function is not supported in VQL. Removing the alias names from the inner Nest operations in your VQL will resolve the error but the output will have only Null values. For more details on the Nest function you could take a look at [Aggregate functions](https://community.denodo.com/docs/html/browse/latest/vdp/vql/appendix/syntax_of_condition_functions/aggregation_functions#nest) Virtual DataPort guide. Hope this helps.
Denodo Team
28-10-2020 05:53:52 -0400
You must sign in to add an answer. If you do not have an account, you can register here