You can translate the question and the replies:

Unable to use row_number() function when JSON as Data source

Hi, I am using JSON as data source. I would like to use row_number() function in my view. Getting data source from API in JSON format. Base view is created from the root JSON array. I have used flattern view to remove the top JSON array. From the flattern view, i am trying to use row_number() function to get the results. Howeve i am having issue with it. Below is the table reference. ID Games Players 1 Cricket James 2 Cricket Joseph 3 Cricket Jacob 4 Cricket Jeps 5 Cricket Jumbo 6 Cricket Jade 7 Football Antony 8 Football Andrea 9 Football Wilson 10 Football Marco 11 Football Lucy 12 Football John I want the following results as output. The games should be listed as single record and the players name should be in comma seperated. Only the top 5 records should be concatenated in the Players field. Please find the below result example: | ID | Games | Players | | -------- | -------- | -------- | | 1 | Cricket | James,Joseph,Jacob,Jeps,Jumbo | | 2 | Football| Antony,Andrea,Wilson,Marco,Lucy | It would be really great if you could provide me some alternative. As i am unable to use row_number function restricting only first 5 records from players as output.
user
12-12-2022 03:02:37 -0500
code

3 Answers

Hi, In Denodo, you could concatenate column values using [**GROUP_CONCAT()**](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/aggregation_functions/aggregation_functions#group-concat) function in Virtual DataPort. In order to achieve your scenario, you could execute the following query in VQL Shell as shown below: **SYNTAX** `SELECT GROUP_CONCAT('<separator>',<field_name>), <columns> FROM <table_name> GROUP BY <column>` **EXAMPLE** ``` SELECT GROUP_CONCAT(',',players) AS PLAYER, GAMES FROM <table_name> GROUP BY GAMES ``` If you would like to use the GROUP_CONCAT function with more parameters, I suggest using a subquery in the FROM clause. If you require further assistance and if you are a valid support user, you could create a support case on [Denodo Support Site](https://support.denodo.com/) so that our team will help you. Hope this helps!
Denodo Team
12-12-2022 06:49:09 -0500
code
Hi, Thank you for the information! I am able to do the group_concat(), and i could see that data are conatenated. However i do not want all the rows to be concatenated. I want only the first 5 rows of games concatenated for each value. From the above example, there are 6 records for Cricket and 6 records for Football. In the result i need only the first 5 rows to be concatenated. I was able to get this outcome when Orcale DB as data source. While creating the base view from DB, I used create base view with query and the view was created successfully. With JSON as data source, i have this issue when creating it from derived view. Finished with error: Error executing view: Function row_number() is not executable.
user
12-12-2022 07:08:14 -0500
Hi, The error you reported **'Function row_number is not executable’** usually occurs when the Virtual DataPort could not delegate the analytic function ‘Row_number’ to the underlying database. The Virtual Dataport can push the analytic functions into the data source but cannot execute them. The Virtual DataPort Server may not be able to delegate analytic functions in the following scenarios: 1. When the underlying data source does not support analytic functions. 2. When the execution engine tries to apply these functions over the data coming from different data sources, even if those data sources support analytic functions. You could take a look at the possible workarounds discussed in [Workaround to Execute Analytic Functions](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/analytic_functions/analytic_functions#workaround-to-execute-analytic-functions) section of Virtual DataPort VQL Guide Hope this helps!
Denodo Team
16-12-2022 01:39:56 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here