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

Question

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.

• user1822: No, that's not possible. The number and types of all columns of a SELECT statement must be known to the database before the statement is actually executed, i.e. by just looking at the SQL itself. Your best option is to write a little PL/pgSQL function that analyzes the JSON values and the re-creates the views based on the keys.

Answer 1

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.