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
Hi @Jurgen_Zornig,
something like this could potentially fulfil your scenario:
select string_agg(col1, ', ') from (unnest(['a','b'])) limit 100;
Applied to a table it would work like this:
select string_agg(col1, ', ') from (select unnest(arr) as col1 from arr where id = <id>) t limit 100;
Hope this helps
Alternative with UDF (for TEXT elements only)
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);}';
SELECT array_to_string(['a','b','c'],',');
-- a,b,c
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()
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!
do it 
btw I also created an issue to support it natively.
Seems like there already is a PR