0

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.

Joshua Rajandiran
  • 2,788
  • 7
  • 26
  • 53

1 Answers1

1

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)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Those are hard coded, I'm looking for something that selects the data of `code` & using it as the key & looping through the entire records – Joshua Rajandiran Sep 14 '17 at 09:23