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?

Patrick · Accepted Answer · 2015-09-17 07:44:48Z

6

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;

answered Sep 17, 2015 at 7:44

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

1 Comment

mech Over a year ago

Tks, it works on this form. I was actually trying to use it at to_tsvector. setweight(to_tsvector(coalesce(array_to_string(array(jsonb_array_elements(new.questions)->>'text'), ', '), '')), 'D');

chkur · Accepted Answer · 2021-11-17 21:27:05Z

0

Sorry, can't comment. Topic starter needed tsvector column. I had such structure for text column named article with json data and needed search text in the key text:

{"time": "some time_structure", "data": {"blocks": [{"data": {"text": "Some long, long text", "image": "image_url"}}, {"data": {"text": "another long text"}}]}}

I generated tsvector field such way:

setweight(to_tsvector('russian'::regconfig, jsonb_path_query_array(article::jsonb, '$.blocks[*].data.text')), 'C')

answered Nov 17, 2021 at 21:27

chkur

135 bronze badges

Collectives™ on Stack Overflow

Merge JSON array to a single string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related