有没有办法根据表格行数据在视图中生成列? [英] Is there a way to generate columns in a view based on table row data?
问题描述
我有这个表格,其中包含应用程序&我只是想在视图中显示它。每个设置的数据都存储为一行。
代码(varchar64)|值(varchar1000)
----------------------
ALLOW_MAC_ADDR | 1
----------------------
ALLOW_SAVE | 1
----------------------
USER_ALIAS | James
现在这个地方变得非常复杂,我必须将这些行转换为 jsonb
在视图中。 value
列名的关键字必须基于 Code
列数据的值。
以下是一个首选的jsonb示例:
[dt:{ALLOW_MAC_ADDR:1,ALLOW_SAVE:1 ,USER_ALIAS:'James'}]
在我看来,我正在考虑这样做:
SELECT .. FROM generate_jsonb()
那么如何实现这样的jsonb ?
编辑:我使用的是v9.6,如果有帮助的话。
https://www.postgresql.org/docs/ current / static / functions-json.html
聚合函数json_object_agg,它将值对
聚合成一个JSON对象
例如:
t =#创建表tt(代码文本,值文本);
CREATE TABLE
t =#插入tt值('ALLOW_MAC_ADDR',1),('USER_ALIAS','James');
INSERT 0 2
t =#从tt中选择json_object_agg(code,value)
json_object_agg
----------------------------------------- -----------
{ALLOW_MAC_ADDR:1,USER_ALIAS:James}
(1 row)
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.
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)
这篇关于有没有办法根据表格行数据在视图中生成列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!