0

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;
2
  • 2
    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. Sep 14, 2021 at 0:16
  • @ErwinBrandstetter Do you know of a faster way to do this? I'll try to add more info in the post.
    – Sentient
    Sep 14, 2021 at 2:07

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.