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

  dynamicData->dynamicField1 as dynamicField1,
  dynamicData->dynamicField2 as dynamicField2,

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.

  • 2
    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.
    – user1822
    Commented Aug 24, 2020 at 16:04

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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