Psql|如何选择具有列名的数据并将其值排序为JSON

Question

我正在使用postgreSQL 11，我正在寻找一种方法来从"金钱、魅力、运气、智慧、健康"列中选择和排序值，并根据其值将它们分组到primaryAttr(L&XL(或secondaryAttr(m(下。

以下是示例表：[产品]

| **id** |   price  |  money  | charm  |  luck  |  wit  | health |
|--------+----------+---------+--------+--------+-------+--------|
| 158001 |   1500   |    M    |   XL   |    L   |   M   |   M    |
| 158002 |   2000   |    L    |   M    |    M   |   XL  |   L    |

我想得到以下结果：[产品]

|     id    |   price   |               primary_attr                    |                   secondary_attr              |
|-----------+-----------+-----------------------------------------------+-----------------------------------------------|
|   158001  |   1500    |   {"luck": "L", "charm": "XL"}                |   {"wit": "M", "money": "M", "health": "M"}   |
|   158002  |   2000    |   {"wit": "XL", "money": "L", "health": "L"}  |   {"luck": "M", "charm": "M"}                 |

JSON结果：

{ 
"id":"158001",
"price":"1500",
"primaryAttr": {
"charm": "XL",
"luck": "L"
},
"secondaryAttr": {
"money": "M",
"wit": "M",
"health": "M"
}
},
{ 
"id":"158002",
"price":"2000",
"primaryAttr": {
"money": "L",
"wit": "XL",
"health": "L"
},
"secondaryAttr":{
"charm": "M",
"luck": "M"
}
}

我四处寻找解决方案，但还没有找到这样的解决方案。我正在努力把事情整合在一起，看看是否有什么好的解决方案。一旦我取得任何进展，我会在这里发布任何更新。有没有人做过类似的事情，可以为我指明正确的方向？感谢您的帮助和建议！

测试的设置数据我忘了添加这个(感谢@Oto Shavadze(。

create table product(id int, price numeric, money text, charm text, luck text,  wit text, health text);
insert into product  
values 
(158001, 1500, 'M','XL','L','M','M'),
(158002, 2000, 'L','M','M','XL','L');

Answer 1

我想你需要这样的东西：

-- just setup  data 
create table product(
id int, 
price numeric, 
money text, 
charm text, 
luck text,  
wit text, 
health text
);
insert into product
values  
(158001 ,  1500   , 'M','XL','L','M','M'),
(158002 ,  2000   ,  'L','M','M','XL','L');
-- actual query:
select v from 
product
join  lateral 
jsonb_build_object('id', id, 'price', price, 
'primaryAttr', 
case 
when money in('XL','L') then jsonb_build_object('money', money) else '{}'::jsonb
end ||
case 
when charm in('XL','L') then jsonb_build_object('charm', charm) else '{}'::jsonb
end ||
case 
when luck in('XL','L') then jsonb_build_object('luck', luck) else '{}'::jsonb
end ||
case 
when wit in('XL','L') then jsonb_build_object('wit', wit) else '{}'::jsonb
end ||
case 
when health in('XL','L') then jsonb_build_object('health', health) else '{}'::jsonb
end,
'secondaryAttr', 
case 
when money in('M') then jsonb_build_object('money', money) else '{}'::jsonb
end ||
case 
when charm in('M') then jsonb_build_object('charm', charm) else '{}'::jsonb
end ||
case 
when luck in('M') then jsonb_build_object('luck', luck) else '{}'::jsonb
end ||
case 
when wit in('M') then jsonb_build_object('wit', wit) else '{}'::jsonb
end ||
case 
when health in('M') then jsonb_build_object('health', health) else '{}'::jsonb
end
) j(v)
on true;

编辑

select id, price, 
case 
when money in('XL','L') then jsonb_build_object('money', money) else '{}'::jsonb
end ||
case 
when charm in('XL','L') then jsonb_build_object('charm', charm) else '{}'::jsonb
end ||
case 
when luck in('XL','L') then jsonb_build_object('luck', luck) else '{}'::jsonb
end ||
case 
when wit in('XL','L') then jsonb_build_object('wit', wit) else '{}'::jsonb
end ||
case 
when health in('XL','L') then jsonb_build_object('health', health) else '{}'::jsonb
end as primaryAttr,
case 
when money in('M') then jsonb_build_object('money', money) else '{}'::jsonb
end ||
case 
when charm in('M') then jsonb_build_object('charm', charm) else '{}'::jsonb
end ||
case 
when luck in('M') then jsonb_build_object('luck', luck) else '{}'::jsonb
end ||
case 
when wit in('M') then jsonb_build_object('wit', wit) else '{}'::jsonb
end ||
case 
when health in('M') then jsonb_build_object('health', health) else '{}'::jsonb
end as secondaryAttr
from 
product;

演示：https://rextester.com/AXYUL76207

Answer 2

嗯。你可以取消抓取并重新聚合：

select t.id, t.price, a.*
from t cross join lateral
(select array_agg(attr) filter (where val in ('L', 'XL')) as primaryAttr,
array_agg(attr) filter (where val in ('M')) as secondaryAttr             
from (values ('money', t.money), ('charm', t.charm), ('wit', t.wit), ('luck', t.luck), ('health', t.health)
) v(attr, val)
) a;