Skip to content

add sql STRING_AGG function #7910

Closed
Closed
add sql STRING_AGG function#7910
Labels
enhancementNew feature or request

Description

@universalmind303
universalmind303
opened on Oct 23, 2023

Is your feature request related to a problem or challenge?

I'd like to be able to use the equivalent of the postgres function STRING_AGG.

Describe the solution you'd like

select string_agg(first_name, ',') as names from tbl group by last_name

Describe alternatives you've considered

No response

Additional context

a few references on string_agg
https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-string_agg-function/
https://www.postgresql.org/docs/9.0/functions-aggregate.html

Activity

  1. universalmind303
    added
    enhancementNew feature or request
    on Oct 23, 2023

  2. alamb commented on Oct 23, 2023

    @alamb
    alamb
    on Oct 23, 2023
    Contributor

    FWIW string_agg I think is a table function, which DataFusion doesn't really have great support for yet.

    What would you think about implementing something like User Defined Table Functions as a way to implement this? #2177

  3. universalmind303 commented on Oct 23, 2023

    @universalmind303
    universalmind303
    on Oct 23, 2023
    ContributorAuthor

    FWIW string_agg I think is a table function, which DataFusion doesn't really have great support for yet.

    isn't string_agg is an aggregate function similar to ARRAY_AGG which is already a supported function?

    string_agg should be functionally equivalent to the combination of already supported functions
    concat_ws(<delimiter>, array_agg(<expr>)).

    Maybe there's a different string_agg function in other dialects I'm not aware of?

  4. alamb commented on Oct 23, 2023

    @alamb
    alamb
    on Oct 23, 2023
    Contributor

    isn't string_agg is an aggregate function similar to ARRAY_AGG which is already a supported function?

    You are right, that makes sense. I tried it out:

    ❯ create table t as values (make_array([1], [2,3], [4])), (make_array([5], [6]));
0 rows in set. Query took 0.001 seconds.

❯ select * from t;
+--------------------+
| column1            |
+--------------------+
| [[1], [2, 3], [4]] |
| [[5], [6]]         |
+--------------------+
2 rows in set. Query took 0.001 seconds.

❯ select array_agg(column1) from t;
+----------------------------------+
| ARRAY_AGG(t.column1)             |
+----------------------------------+
| [[[1], [2, 3], [4]], [[5], [6]]] |
+----------------------------------+
1 row in set. Query took 0.004 seconds.

  5. haohuaijin commented on Nov 12, 2023

    @haohuaijin
    haohuaijin
    on Nov 12, 2023
    Contributor

    I want to work on this issue.

  6. alamb commented on Aug 12, 2025

    @alamb
    alamb
    on Aug 12, 2025
    Contributor

    This function currently works well

    > select string_agg(v, ':') from (select 'foo'||value as v from generate_series(1,100));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| string_agg(v,Utf8(":"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo1:foo2:foo3:foo4:foo5:foo6:foo7:foo8:foo9:foo10:foo11:foo12:foo13:foo14:foo15:foo16:foo17:foo18:foo19:foo20:foo21:foo22:foo23:foo24:foo25:foo26:foo27:foo28:foo29:foo30:foo31:foo32:foo33:foo34:foo35:foo36:foo37:foo38:foo39:foo40:foo41:foo42:foo43:foo44:foo45:foo46:foo47:foo48:foo49:foo50:foo51:foo52:foo53:foo54:foo55:foo56:foo57:foo58:foo59:foo60:foo61:foo62:foo63:foo64:foo65:foo66:foo67:foo68:foo69:foo70:foo71:foo72:foo73:foo74:foo75:foo76:foo77:foo78:foo79:foo80:foo81:foo82:foo83:foo84:foo85:foo86:foo87:foo88:foo89:foo90:foo91:foo92:foo93:foo94:foo95:foo96:foo97:foo98:foo99:foo100 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.007 seconds.

    So closing down

  7. alamb
    closed this as completedon Aug 12, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions