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;

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