You can translate the question and the replies:

Query report data from Oracle Service Cloud

Hi, I am trying to create a single view of a report obtained via a web service hosted by Oracle Service Cloud. The column names and row values are returned in seperate registers. In a base view query, the column names appear as rows as do the individual row values in the results. It seems that this happens because the row elements have no names in the source where usually the name inthe name value pair would be the column name, Sample data: { "count": 2, "name": "CC Extract", "columnNames": [ "Reference #", "First Name", "Last Name", "Home Phone", "Mobile Phone" ], "rows": [ [ "200427-000001", "User01", "LastName01", null, "27836760000" ], [ "200427-000003", "User02", "LastName02", null, "27826360000" ] ] } I've tried flattening the base view and then referencing the array elements using the SPLIT function to build custom columns without any success. Please assist.
user
30-04-2020 11:15:01 -0400

3 Answers

Hi, I was able to make a series of derived views using the base view of your JSON file that was able to display the data in rows and columns. Here are the steps using the VDP Administration Tool: 1. Create new JSON data source 2. Create base view over JSON data source 3. Right click on your base view, New, Flatten 4. In the Model Tab, right click the second "rows" on the base view and choose "Flatten array rows" 5. In the Output tab, remove all rows except "field_0" and rename this field to "data" 6. In the Output tab, choose "New Field", with Field name of "column_index" and Field expression of "(rownum()%5)" 7. Save flattened view "row_data" 8. Right click on new flattened view, New, Selection 9. In the Where Conditions tab, choose the column_index field = 0 10. In the Output tab, choose "New Field", with Field name of "row" and Field expression of "(rownum()" 11. Save view as "row_data_col_0" 12. Repeat steps 6-9 for the next four columns. The column indexes will be 1, 2, 3, 4; change each new view Where clause and name to correspond to these indexes. The new view names will be "row_data_col_1", "row_data_col_2", "row_data_col_3", "row_data_col_4" 13. Select the five new "row_dat_col_x" views, New, Join 14. In the Model tab, create a join between each view on field "row" 15. In the Output tab, remove all but "data" columns 16. In the Output tab, rename each "data" column to the column name in source * View Name = row_data_1 > Field Name = referenceno * View Name = row_data_2 > Field Name = firstname * View Name = row_data_3 > Field Name = lastname * View Name = row_data_4 > Field Name = homephone * View Name = row_data_0 > Field Name = mobilephone 17.Save and Execute Hope this helps!
Denodo Team
06-05-2020 18:09:40 -0400
Thank you for the response! The creation of the JSON data source, the base view and the flatten view were quite simple. The creation of the selection views were quite tricky with varying outcomes in VDP Admin. I found that the first selection view worked but the second one would return 0 rows immediately without seemingly executing the web service. Even tried scripting the first one and just changing the name - got the same result. Also discovered that if I create a selection view using default name it would more likely work, but if I rename it would then behave the same way and respond immediately with 0 rows. Nevertheless ... I eventually I managed to get 2 select views (col 1 and col 2) working at the same time after multiple attempts, I then tried to join the two as a test. When I executed the join it ran forever until I forced a stop. I can't see how one can join on row value though as the rows values are different between columns? So I added a row number in the flatten view output to get a unique value per record on which to join the selection views. Same result though, i.e. in terms of running for ever ... BTW, I have both 'from' and 'to' date parameters which I also join on so that I am only asked for one set when executing the join. FYI: The column_index value: ((rownum()%5) is visible when executing the flatten view to test, I see that the index starts as 1-based and then switches to 0-based resulting in a column index offset after the first set of records. It doesn't seem to affect how the column is selected in the select views though.
user
07-05-2020 10:06:14 -0400
Hi, I have tested the steps outlined and was able to create the output in columns and rows. Here are some more details about how I tested this using the VDP Administration Tool. • To keep the return set small to ensure good performance, I made a JSON flat file with the data provided above in your question. Make sure the quotes in the file are " (Ascii character 34). • After creating the data source to this file, create the base view ("bv_json"). Executing query, results were: | count | name | columnnames | rows | | -------- | -------- | -------- | | 2 | CC Extract | [Array]… | [Array]… | • I then created the flattened view “row_data”. I verified the VQL definition: `CREATE OR REPLACE VIEW row_data AS SELECT field_0 AS data, (rownum()%5) AS column_index FROM FLATTEN bv_json AS v ( v.rows.rows);` • Executing query, results were: | data | column_index | | -------- | --------| | 200427-000001| 1 | | User01| 2 | | LastName01| 3 | | <<null>>| 4 | | 27836760000| 0 | | 200427-000003| 1 | | User02| 2 | | LastName02| 3 | | <<null>>| 4 | | 27836760000| 0 | • For row_data_col_0 view, I verified the VQL `CREATE OR REPLACE VIEW row_data_col_0 AS SELECT row_data.data AS data, row_data.column_index AS column_index, rownum() AS "row" FROM row_data WHERE column_index = 0;` • Executing query row_data_col_0, results were: | data | column_index |row| | -------- | --------|--------| | 278367600000| 0 |1 | | 278367600000| 0 |2 | • For row_data_col_1 view, I verified the VQL `CREATE OR REPLACE VIEW row_data_col_1 AS SELECT row_data.data AS data, row_data.column_index AS column_index, rownum() AS "row" FROM row_data WHERE column_index = 1;` • Executing query row_data_col_1, results were: | data | column_index |row| | -------- | --------|--------| | 200427-000001| 1 |1 | | 200427-000003| 1 |2 | • For row_data_col_2 view, I verified the VQL `CREATE OR REPLACE VIEW row_data_col_2 AS SELECT row_data.data AS data, row_data.column_index AS column_index, rownum() AS "row" FROM row_data WHERE column_index = 2;` • Executing query row_data_col_2, results were: | data | column_index | row | | -------- | -------- | -------- | | User01 | 2 | 1 | | User02 | 2 | 2 | • For row_data_col_3 view, I verified the VQL `CREATE OR REPLACE VIEW row_data_col_3 AS SELECT row_data.data AS data, row_data.column_index AS column_index, rownum() AS "row" FROM row_data WHERE column_index = 3;` • Executing query row_data_col_3, results were: | data | column_index |row| | -------- | --------|--------| | LastName01| 3 |1 | | LastName03| 3 |2 | • For row_data_col_4 view, I verified the VQL `CREATE OR REPLACE VIEW row_data_col_4 AS SELECT row_data.data AS data, row_data.column_index AS column_index, rownum() AS "row" FROM row_data WHERE column_index = 4;` • Executing query row_data_col_4, results were: | data | column_index |row| | -------- | --------|--------| | <<null>>| 4 |1 | | <<null>>| 4 |2 | • Finally, for the combined column view, I verified the VQL `CREATE OR REPLACE VIEW row_data_combined AS SELECT row_data_col_0.data AS mobilephone, row_data_col_1.data AS referenceno, row_data_col_2.data AS firstname, row_data_col_3.data AS lastname, row_data_col_4.data AS homephone FROM (((row_data_col_0 AS row_data_col_0 INNER JOIN row_data_col_1 AS row_data_col_1 ON row_data_col_0."row" = row_data_col_1."row" ) INNER JOIN row_data_col_2 AS row_data_col_2 ON row_data_col_1."row" = row_data_col_2."row" ) INNER JOIN row_data_col_3 AS row_data_col_3 ON row_data_col_2."row" = row_data_col_3."row" ) INNER JOIN row_data_col_4 AS row_data_col_4 ON row_data_col_3."row" = row_data_col_4."row" ;` • Executing query row_data_combined, results were: | mobilephone | referenceno |firstname| lastname| homephone| | -------- | --------|--------|--------|--------| | 200427-000001| User01 |LastName01 |<<null>> |27836760000 | | 200427-000003| User02 | LastName02 |<<null>> |27836760000 | Hope this helps.
Denodo Team
19-05-2020 15:49:38 -0400
You must sign in to add an answer. If you do not have an account, you can register here