# Errors when using array functions with column data from CSV in array format

**URL:** <https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751>\
**Category:** Uncategorized\
**Created:** [February 5, 2025, 3:52pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751 "2025-02-05T15:52:31Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![gray](https://avatars.discourse-cdn.com/v4/letter/g/5fc32e/32.png) [@gray](https://community.dremio.com/u/gray)\
**Post date:** [February 5, 2025, 3:52pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751/1 "2025-02-05T15:52:32Z")

</div>

Hello,

We’ve got some csv files coming in that have columns of array data like this example column “hobbies”:

```auto
"[
  ""Volunteering"",
  ""Traveling"",
  ""Running""
]"

```

When I try to access the column using array functions I get various errors.

This works:

```auto
select array_contains(convert_from('["Swimming","Travel","Fishing"]','json'),'Travel');

```

But this fails with “_‘ARRAY\_CONTAINS’ expects an ARRAY, but instead got: ANY_”:

```auto
select array_contains(convert_from(hobbies,'json'),'Travel') from ArrayTesting;

```

This works:

```auto
select array_slice(convert_from('["Swimming","Travel","Fishing"]','json'),0,3);

```

This fails with “_‘ARRAY\_SLICE’ does not accept the supplied operand types: ANY, INTEGER, INTEGER_”:

```auto
select array_slice(convert_from(hobbies,'json'),0,3) from ArrayTesting;

```

Visual comparison of the two looks like the arrays match when selected like this:

 ![{D568B9EE-0F2A-4A06-9965-FC691132DFFC}](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/d491c72d5e86d25b78c6528dcc2e198556cdbd90.png)

Comparing the two convert\_from values returns true, again implying that they match:

```auto
select 
    case when 
        convert_from('["Swimming","Travel","Fishing"]','json') = convert_from(hobbies,'json')
        then 'true'
        else 'false'
    end
FROM ArrayTesting;

```

Is there something under the hood that’s treating these as the same in this instance, but not when used in array functions? Do I need to do some additional conversion to make this compatible?

Below are various tests I’ve run just to see how things respond.

```auto
/*
Result: Success
Output:
    EXPR$0
    ["Swimming","Travel","Fishing"]
*/
select convert_from('["Swimming","Travel","Fishing"]','json');

/*
Result: Success
Output:
    EXPR$0
    ["Swimming","Travel","Fishing"]
*/
select convert_from(hobbies,'json')
from ArrayTesting;

/*
Compare the two convert_from values
Result: Values appear to match
Output:
    EXPR$0
    true
*/
select 
    case when 
        convert_from('["Swimming","Travel","Fishing"]','json') = convert_from(hobbies,'json')
        then 'true'
        else 'false'
    end
FROM ArrayTesting;

/*
Result: Success
Output:
    EXPR$0
    true
*/
select array_contains(convert_from('["Swimming","Travel","Fishing"]','json'),'Travel');

/*
Result: Fail
Output:
    'ARRAY_CONTAINS' expects an ARRAY, but instead got: ANY
*/
select array_contains(convert_from(hobbies,'json'),'Travel')
from ArrayTesting;

/*
Result: Success
Output:
    EXPR$0
    3
*/
select array_length(convert_from('["Swimming","Travel","Fishing"]','json'));

/*
Result: Fail
Output:
    'ARRAY_LENGTH' does not accept the supplied operand types: ANY
*/
select array_length(convert_from(hobbies,'json'))
from ArrayTesting;

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [February 6, 2025, 4:57pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751/2 "2025-02-06T16:57:52Z")

</div>

@gray By any chance would you be able to generate the same in JSON or parquet and see if you hit the same planning issue, trying to narrow down if it is specific to CSV. I will see if we can reproduce this behavior

---

<div class="post-metadata">

**Author:** ![gray](https://avatars.discourse-cdn.com/v4/letter/g/5fc32e/32.png) [@gray](https://community.dremio.com/u/gray)\
**Post date:** [February 7, 2025, 4:39pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751/3 "2025-02-07T16:39:34Z")

</div>

@balaji.ramaswamy ,

I converted the CSV to JSON and I’m seeing the same issue; both Query 5 and Query 7 fail.

The test JSON file created just contains

```auto
{
  "ID": "1",
  "HOBBIES": "[\n \"Swimming\",\n \"Travel\",\n \"Fishing\"\n]"
}

```

I thought maybe the \n was problematic, so stripped it out, but this also has the same results:

```auto
{
  "ID": "1",
  "HOBBIES": "[\"Swimming\",\"Travel\",\"Fishing\"]"
}

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [February 10, 2025, 3:28pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751/4 "2025-02-10T15:28:56Z")

</div>

@gray Are you able to send a sample file for us to investigate

---

<div class="post-metadata">

**Author:** ![gray](https://avatars.discourse-cdn.com/v4/letter/g/5fc32e/32.png) [@gray](https://community.dremio.com/u/gray)\
**Post date:** [February 10, 2025, 3:50pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751/6 "2025-02-10T15:50:18Z")

</div>

@balaji.ramaswamy ,

Sure thing, I’ve sent the files to you directly.

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [February 11, 2025, 4:35pm UTC](https://community.dremio.com/t/errors-when-using-array-functions-with-column-data-from-csv-in-array-format/12751/7 "2025-02-11T16:35:52Z")

</div>

Thanks @gray Will get back to you in a couple of days