Sample Header Ad - 728x90

PostgreSQL Convert JSONB array to string

1 vote
1 answer
19675 views
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](https://dba.stackexchange.com/a/54289/238642) , 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;
Asked by Sentient (111 rep)
Sep 13, 2021, 09:38 PM
Last activity: May 14, 2023, 02:10 AM