You can translate the question and the replies:

Function to select from an array

Hello, I have an array with the following structure: {"description":null,"sourcesystem":"Midas"} and then another array with this structure: {"productmaintype":{"productmaintype":"Currency Investment Account"},"productsubtype":{"productsubtype":"Resident"}} 1. I have tried to flatten the view by creating a flattened view from the derived view but the flatten field option is greyed out for these fields. 2. I have also tried to project the fields on the output tab by right-clicking but that option is not visible either. Are there functions that can be used to select a specific value from these arrays? So I want to select the Midas part from the first array and the Currency Investment Account part from the second array. Thank you
user
18-09-2020 07:39:02 -0400

1 Answer

Hi, From the example that you provided, it looks like you are working with registers in your data source. When I imported your examples into Denodo using a JSON data source and selected the JSON root when creating a base view, I was able to create a selection view and then project the subfields of the register by right clicking on the field in the output tab of the view. Regarding your question about searching within a register, I would usually recommend projecting the register before searching in it; however, I was able to search in your register using the (field).subfield format in a WHERE statement: ``` (productmaintype).productmaintype = ‘Currency Investment Account’ ``` Since you are unable to project your subfields, I would make sure of the following: 1. That my fields have been correctly imported as an array or register type. To do this, you can look for the field type in the output screen of the view and make sure that it shows up as something like “<source_name>_productmaintype”; the type will also have an icon with four boxes next to it. If it does not show up like this, then I would double check that the source is formatted correctly to output a register response. 2. That I am creating a selection view before trying to project the subfields of my register. The subfields of a register cannot be projected in a base view. For more information about compound types, you can refer to the [Management of Compound Values](https://community.denodo.com/docs/html/browse/latest/vdp/vql/advanced_characteristics/management_of_compound_values/management_of_compound_values#management-of-compound-values) section of the Virtual DataPort VQL Guide. If you are still having trouble flattening your compound fields and you have an active support account, I recommend that you open a ticket with the support team so that they can analyze the behavior in more detail and help you to resolve it. I hope this helps!
Denodo Team
23-09-2020 14:29:20 -0400
You must sign in to add an answer. If you do not have an account, you can register here