1

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.

1
  • 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. – a_horse_with_no_name Aug 24 '20 at 16:04
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, privacy policy and cookie policy

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