You can translate the question and the replies:

Read an element in an array

Hi Team, How to read the elements of an array at specific index? I have also tried using flatten, but i am unable to achieve.Kindly help me on this. e.g : array = {'a','b','c'} i want to read the element at specific index, for eg : at index 1 , output is b Also how to find the nth occurrence of a character in a string?
user
16-06-2023 02:30:09 -0400
code

1 Answer

Hi, Based on your example, I assume you’re trying to read an array from a normal text file rather than a JSON file. Therefore, when you create your base view, your column which contains an array is treated as a `Text` rather than a `JSON array`. This is expected behaviour in Denodo. Since it’s a text file, all the fields will be treated as text disregarding if there are arrays or registers inside. You need to have a field with a data type `array` or `JSON array` to be able to query an element at a specific index or flatten the view. I suggest that you create a derived view and use the function [SPLIT](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/text_functions/text_functions#split) on your array field, which will split the value based on a string expression and return it as an array. This would convert your text field to an array, which will allow you to further flatten it or query specific indexes. Please take note that since it’s a text field, some data manipulations might be required to cleanse and format your values correctly like removal of brackets, quotations, etc. You can check out all the available [Text functions](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/text_functions/text_functions) you can utilise in Denodo. Once it’s done, you can now query the specific element based on index from the view like below: ``` SELECT id,name,array[0].string as array_value FROM dv_employee ``` For more information about compound types, you can refer to the user guide [Management of Compound Values](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/advanced_characteristics/management_of_compound_values/management_of_compound_values). Hope this helps!
Denodo Team
26-06-2023 02:23:51 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here