# Array to string

**URL:** <https://community.cratedb.com/t/array-to-string/641>\
**Category:** SQL\
**Created:** [April 19, 2021, 10:09am UTC](https://community.cratedb.com/t/array-to-string/641 "2021-04-19T10:09:54Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jurgen\_Zornig](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jurgen_zornig/32/183_2.png) [@Jurgen\_Zornig](https://community.cratedb.com/u/Jurgen_Zornig)\
**Post date:** [April 19, 2021, 10:09am UTC](https://community.cratedb.com/t/array-to-string/641/1 "2021-04-19T10:09:54Z")

</div>

As far as I read the docs (and tried it out myself for sure) there seems no possibility to cast an array field value to a string.

In my case I have an array of strings type field which I just want to print into a string field separated by comma. `string_agg()` doesn’t accept arrays and `array_to_string()` doesn’t exist in cratedb.

Any advice appreciated

---

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [April 19, 2021, 10:19am UTC](https://community.cratedb.com/t/array-to-string/641/2 "2021-04-19T10:19:27Z")

</div>

Hi @Jurgen_Zornig,

something like this could potentially fulfil your scenario:

```auto
select string_agg(col1, ', ') from (unnest(['a','b'])) limit 100;

```

Applied to a table it would work like this:

```auto
select string_agg(col1, ', ') from (select unnest(arr) as col1 from arr where id = <id>) t limit 100;

```

Hope this helps

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 19, 2021, 10:26am UTC](https://community.cratedb.com/t/array-to-string/641/3 "2021-04-19T10:26:02Z")

</div>

Alternative with UDF (for TEXT elements only)

```sql
CREATE OR REPLACE function array_to_string(array(TEXT),TEXT)
RETURNS TEXT
LANGUAGE JAVASCRIPT
AS 'function array_to_string(arr, delimiter) { return Array.prototype.join.call(arr, delimiter);}';

```

```sql
SELECT array_to_string(['a','b','c'],',');
-- a,b,c 

```

also see [User-defined functions — CrateDB: Reference](https://crate.io/docs/crate/reference/en/4.5/general/user-defined-functions.html#working-with-array-methods)

---

<div class="post-metadata">

**Author:** ![Jurgen\_Zornig](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jurgen_zornig/32/183_2.png) [@Jurgen\_Zornig](https://community.cratedb.com/u/Jurgen_Zornig)\
**Post date:** [April 19, 2021, 12:28pm UTC](https://community.cratedb.com/t/array-to-string/641/4 "2021-04-19T12:28:29Z")

</div>

> [@jayeff](#):
>
> `select string_agg(col1, ', ') from (select unnest(arr) as col1 from arr where id = <id>) t limit 100;`

Well, yes that works, at least for a single record…

I have to render 20Mio Records from a plain table to a JSON Document Format. Crate also seems to have trouble with nested queries and referencing alias tables, and last but not least the temp table coming out of unnest() seems to bloat memory until there is nothing left when I do not limit it to some thousands of records.

I understand that I might have to tweak your approach a little more, perhaps it would work then. I hoped it might be easier. Thanks for the hint anyway, wouldn’t have thought of unnest()

---

<div class="post-metadata">

**Author:** ![Jurgen\_Zornig](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jurgen_zornig/32/183_2.png) [@Jurgen\_Zornig](https://community.cratedb.com/u/Jurgen_Zornig)\
**Post date:** [April 19, 2021, 12:31pm UTC](https://community.cratedb.com/t/array-to-string/641/5 "2021-04-19T12:31:04Z")

</div>

I`m still in the pre-Opensourced-crate era on cratdb-Community edition without UDF support, but I gues thats the trigger event now to upgrade and get make my crate usage more procedural.

Thanks!

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 19, 2021, 12:34pm UTC](https://community.cratedb.com/t/array-to-string/641/6 "2021-04-19T12:34:13Z")

</div>

do it 😉

btw I also created an issue to support it natively.

> <https://github.com/crate/crate/issues/11284>
>
> Use case:
> Support for PG array\_to\_string() function in CrateDB.
> This now can be done as workaround with an UDF for TEXT:
> CREATE OR REPLACE...

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 23, 2021, 6:18am UTC](https://community.cratedb.com/t/array-to-string/641/7 "2021-04-23T06:18:12Z")

</div>

@Jurgen_Zornig

Seems like there already is a PR 😉

> <https://github.com/crate/crate/pull/11308>
>
> \## Summary of the changes / Why this improves CrateDB
> 
> Resolves #11284
> 
> \## C…hecklist
> 
> - \[X\] Added an entry in \`CHANGES.txt\` for user facing changes
> - \[X\] Updated documentation & \`sql\_features\` table for user facing changes
> - \[X\] Touched code is covered by tests
> - \[X\] \[CLA\](https://crate.io/community/contribute/cla/) is signed
> - \[X\] This does not contain breaking changes, or if it does:
> - It is released within a major release
> - It is recorded in \`\`CHANGES.txt\`\`
> - It was marked as deprecated in an earlier release if possible
> - You've thought about the consequences and other components are adapted
> (E.g. AdminUI)