CODEWITHSUNDEEP
sqlpostgresql
Radex
Radex

Reputation: 8587

How to concatenate strings provided by jsonb_array_elements() in PostgreSQL?

I need to concatenate string returned by jsonb_array_elements() as a single column in my result.

I am using PostgreSQL v.12.1

Her my SQL:

SELECT
  name as name,
  jsonb_array_elements(payload -> 'pr' -> 'act') -> 'tags' -> 'Content' ->> 0 AS ws
FROM
  data

Current produce this result:

name   ws   
  A       1
  A       2
  A       3
  B       4

I would like to have this result instead:

name   ws
 A       1, 2, 3
 B       4

Upvotes: 1

Views: 533

Answers (1)

jjanes
jjanes

Reputation: 44202

One way is to wrap your query with an outer query:

select name, string_agg(ws, ', ') from (<your query here>) foo group by name

Another would be to move the function call from the select-list into the from-list (as a lateral join, but LATERAL is implicit with function calls), which should make it available for the aggregate. You didn't give CREATE TABLE or INSERT statements with example data, and I'd rather not post something I can't test, so no example of this is provided.

Upvotes: 1

Related Questions