How to flatten out nested array of strings in json column?-CodePudding

I have the following table:

id	contents
123	{ blocks: [{ text: "abc" }, { text: "123" }] }
foo	{ blocks: [{ text: "bar" }, { text: "moretext" }, { text: "ok" }] }

I want to write a view of the above that looks like:

id	contents	raw_text
123	{blocks: [{text: "abc"}, {text: "123"}]}	abc, 123
foo	{blocks: [{text: "bar"}, {text: "moretext"}, { text: "ok"}]}	bar, moretext, ok

This was the query I tried running:

select post.id, array_to_string(array_agg(jsonb_array_elements(post.contents -> 'blocks') ->> 'text')) as paragraphs from post group by id

But it results in the error

aggregate function calls cannot contain set-returning function calls.

CodePudding user response：

If a JSON array of all the values is also acceptable, you can use a JSON path query:

select id, contents, 
       jsonb_path_query_array(contents, '$.blocks[*].text')
from post;

As there is no simply cast from a JSON array to a native Postgres array, and you do need that as a CSV string, you need to unnest and aggregate with a scalar sub-query:

select id, contents, 
       (select string_agg(x.item ->> 'text', ', ')
        from jsonb_array_elements(contents -> 'blocks') as x(item)) as raw_text
from post;

The reason for your error is, that you are mixing nesting multiple aggregate and set returning function which simply isn't supported.