Sort conditional data in Postgres STRING_AGG

Question

I've come up with a query to create concatenated string based on a conditional statement using STRING_AGG in Postgress. That works just fine but I want to sort the result as well without duplicating the CASE.

This is what I have now:

SELECT
STRING_AGG(distinct name, ', ' ORDER BY name),
STRING_AGG(
  DISTINCT
  CASE WHEN something = true THEN 'type1'
       WHEN something_else = true THEN 'type2'
       ELSE 'type3'
  END, ', '
ORDER BY
  CASE WHEN something = true THEN 'type1'
       WHEN something_else = true THEN 'type2'
       ELSE 'type3'
  END

)
from organisations

..but I want to do something like this to not have the duplicated code and remove rows and complexity from my query but I can't figure out how to make it work, this is fake code that doesn't work obviously, but you get the idea:

SELECT
STRING_AGG(distinct name, ', ' ORDER BY name) names,
STRING_AGG(
  DISTINCT (
    CASE WHEN something = true THEN 'type1'
         WHEN something_else = true THEN 'type2'
         ELSE 'type3'
    END
  ) as types, ', ' ORDER BY types) types
from organisations

Gordon Linoff · Accepted Answer · 2018-04-15 14:15:59Z

2

You don't require string_agg(). You could instead do:

SELECT STRING_AGG(distinct name, ', ' ORDER BY name) names,
       CONCAT_WS(',',
                 (CASE WHEN SUM( (something = true)::int ) > 0 THEN 'type1'),
                 (CASE WHEN SUM( (not (something = true) )::int ) > 0 THEN 'type2')
                ) as types
FROM organisations o;

You may have oversimplified the query, but for what you provided, you don't need string_agg(distinct) for the second portion.

answered Apr 15, 2018 at 14:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pean Over a year ago

That is great, I could make your suggestion work for me, I would not have figured that one out myself. Thank you!

Tim Biegeleisen · Accepted Answer · 2018-04-15 14:11:38Z

1

One option might be to compute the CASE expression first in a separate CTE, and then query that CTE to apply STRING_AGG:

WITH cte AS (
    SELECT
        name,
        CASE WHEN something = true THEN 'type1'
             WHEN something_else = true THEN 'type2'
             ELSE 'type2'
        END AS expr
    FROM organisations
)

SELECT
    STRING_AGG(distinct name, ', ' ORDER BY name),
    STRING_AGG(DISTINCT expr, ', ' ORDER BY expr)
FROM cte;

As a minor side note, since you have type2 as both the second and ELSE condition in the CASE expression, you might as well just use this:

CASE WHEN something = true THEN 'type1'
     ELSE 'type2'
END

answered Apr 15, 2018 at 14:11

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

pean Over a year ago

Actually, the type2 in in the ELSE was a typo, thanks for pointing that out, I've not corrected it.

Collectives™ on Stack Overflow

Sort conditional data in Postgres STRING_AGG

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related