Search code examples
postgresqlfunctionviewjsonb

Is there a way to generate columns in a view based on table row data?

I have this table which contains the settings of an app & I just want to show it in the view. The data of each setting is stored as a row.

Code (varchar64)| Value (varchar1000)
----------------------
ALLOW_MAC_ADDR  | 1
----------------------
ALLOW_SAVE      | 1
----------------------
USER_ALIAS      | James

Now this is where it gets kinda complicated, I have to convert these rows into a jsonb at the view. The key for value column name has to be based on the value of the Code column data.

Here is an example of prefered jsonb:

[dt:{ALLOW_MAC_ADDR: 1, ALLOW_SAVE: 1, USER_ALIAS: 'James'}]

I'm thinking of doing some like this in my view:

SELECT .. FROM generate_jsonb()

So how do I achieve such jsonb?

EDIT: I'm using v9.6 if that helps.

Solution

  • https://www.postgresql.org/docs/current/static/functions-json.html

    aggregate function json_object_agg which aggregates pairs of values into a JSON object

    eg:

    t=# create table tt(code text, value text);
CREATE TABLE
t=# insert into tt values('ALLOW_MAC_ADDR',1),('USER_ALIAS','James');
INSERT 0 2
t=# select json_object_agg(code,value) from tt;
                  json_object_agg
----------------------------------------------------
 { "ALLOW_MAC_ADDR" : "1", "USER_ALIAS" : "James" }
(1 row)