3

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.

Improve this question
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. Commented Aug 24, 2020 at 16:04

1 Answer 1

Reset to default
4

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.

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.