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

Question

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:

I would like to have this result instead:

name   ws
 A       1, 2, 3
 B       4

jjanes · Accepted Answer · 2019-11-28 17:02:13Z

1

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.

answered Nov 28, 2019 at 17:02

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related