Can I select keys stored in a postgres jsonb column as values dynamically

I have a JSONB column in a Postgres DB that contains some dynamic data about a row. Think of the table as a single table with an id column and a "dynamicData" JSONB column for simplicity for this example. And suppose in a row, I store the following in the "dynamicData" column

{
  dynamicField1: 'value1',
  dynamicField2: 'value2',
}

I understand I can select those as columns in a select such as

Select
  dynamicData->dynamicField1 as dynamicField1,
  dynamicData->dynamicField2 as dynamicField2,
From 
  some_table

But what I'm curious is if I can somehow tell PG I want all keys in the JSONB column to be selected as columns.

This is because I have database views that sit on top of the tables, and if I add a new JSONB key in the dynamic data column, I would then need to somehow update these views if I cannot select the keys as columns automatically.

Like @a_horse told you, SQL is strictly typed and demands to know the resulting row type at call time at the latest.

If key names follow a predictable pattern, and there can only be a trivial number, you might "overprovision" in your views:
SELECT dynamic_data->'dynamicField1' AS dynamicField1
, dynamic_data->'dynamicField2' AS dynamicField2
, dynamic_data->'dynamicField3' AS dynamicField3 -- overprovisioned columns
, ...
, dynamic_data->'dynamicField3' AS dynamicField9
FROM (SELECT json '{"dynamicField1": "value1"
, "dynamicField2": "value2"}') tbl(dynamic_data);


Missing keys get a NULL value. dynamicField3 - dynamicField9 are NULL in the example. The workaround has obvious weaknesses.

StackExchange Database Administrators Q#274252, answer score: 3