PostgreSQL Convert JSONB array to string

Question

I am trying to flatten a nested JSONB object into a string so that I can run regex on it.

To retrieve the relevant text fields, I'm using

SELECT jsonb_path_query_array(posts.content, 'strict $.**.text') FROM posts

which returns a jsonb (array). I've tried naively casting the jsonb to ::text[] and then array_to_string, but that results in the following type error:

cannot cast type jsonb to text[]. null

I have also tried wrapping the result in a jsonb_array_elements_text, but that gives me a list of rows for all posts instead of just one.

Edit: Following this answer, I've come up with

SELECT posts.post_id, plaintext.plaintext
FROM posts, LATERAL (
 SELECT string_agg(value::text, ', ') AS plaintext
 FROM jsonb_array_elements_text(jsonb_path_query_array(posts.content, 'strict $.**.text'))
 ) plaintext;

• Erwin Brandstetter: You might answer your own question instead of editing an answer into the question. Or do you want to close it? Or you provide the actual regex pattern you want to run. Chances are, there are faster solutions. This transformation is rather costly.
• Sentient: @ErwinBrandstetter Do you know of a faster way to do this? I'll try to add more info in the post.

Answer 1

It is a bit counterintuitive.
This worked for me in postgres 13:

Get an array:

ARRAY(SELECT jsonb_array_elements_text( 
 jsonb_path_query_array(table.jsonb_column, '$.item.*.subitem')
))

Get an array into a string:

array_to_string(ARRAY(SELECT jsonb_array_elements_text( 
 jsonb_path_query_array(table.jsonb_column, '$.item.*.subitem')
)), ' ')