CODEWITHSUNDEEP
jsonpostgresqljsonb
mech
mech

Reputation: 674

Merge JSON array to a single string

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?

Upvotes: 9

Views: 14702

Answers (2)

chkur
chkur

Reputation: 13

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')

Upvotes: 0

Patrick
Patrick

Reputation: 32234

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;

Upvotes: 6

Related Questions