So imagine I have a table with a jsonb column data (for the sake of example, not because it’s a good column name) that contains an array of json objects with a key states that is an array of USA state abbreviations:

[ { states: ['AZ', 'CA'], ... }, { states: ['NY','CO'], ... }, ... ]

I’d like to aggregate all the states in the JSON objects into a list of distinct states and make it into a generated stored column states.

One not great way to select such a list is:

select jsonb_path_query_array(
  ( select jsonb_agg(b) from
    (select distinct jsonb_array_elements(a) as state from jsonb_array_elements(
       jsonb_path_query_array(data, '$[*].states')
    ) as a) as b
  ), '$[*].state') as states
from myTable

This (a) seems overly complicated for something as simple as mapping an array by the states key and calling whatever the PG equivalent of flatten is, and (b) has select in it, and so can’t be used for generated columns.

The other ways I can imagine doing it all involve lateral joins.

Given that all the data is in a column, it seems like it should be possible to do some kind of transformation on it without selects or joins for creating a generated stored column. But I can’t seem to figure out how to do this without using select and/or join lateral.

Is there a way to do this for a generated stored column in PostgreSQL?

(Currently on PG 16.x)