Merge JSON array to a single string

Question

I have a field named qn of JSONB type of this form:

[{id: 1, text: 'A'}, {id: 2, text: 'B'}]

To get all the text, I can do:

SELECT jsonb_array_elements(qn)->>'text' from templates where id=1

My question is, how can I merge into a single string like this:

A, B

If the field is not JSONB, it can be done easily using:

SELECT array_to_string(ARRAY(select title from templates), ', ');

How do you do it if the field is JSONB?

Answer 1

Simply aggregate into a string:

SELECT string_agg(txt, ', ') AS all_text
FROM (
  SELECT jsonb_array_elements(qn)->>'text' AS txt
  FROM templates
  WHERE id = 1) sub;