时间戳

首页 > 解决方案 > 根据 PostgreSQL 查询中数组列表的值转换列中的值（数组数据类型）

arrays - 根据 PostgreSQL 查询中数组列表的值转换列中的值（数组数据类型）

问题描述

我需要你对 Postgresql 查询逻辑的帮助

说我有一张桌子

环境信息： AWS Aurora Postgresql db 10.6.x

CREATE TABLE test_table
(id               character varying(50)
name              character varying(128) 
original_value    ARRAY
);

带值的表

 id                   name             original_value
O1S000000000301       Screw            {metal_fabtication_c,cabinetery_andor_shelves_c,table_c}
O1S000000000302       wrench           {carpentry_c,handyman_c}
O1S000000000303       impact_driver    {carpentry_c,masonry_c,handyman_c}

Transformed_value 不是表，但它具有 Name 及其对应的 Transformation，如下所示

Name                        Value
metal_fabrication_c         Metal Fabrication
cabinetry_andor_shelves_c   Cabinetry/Shelving
handyman_c                  Handyman
carpentry_c                 Carpentry
masonry_c                   Masonry
table_c                     Furniture

我必须编写一个查询，其中 Original_value 应该得到这个等效的transformed_Value。

SELECT id, name, original_value as transformed_value 
 FROM test_table 
WHERE id IN('O1S000000000301','O1S000000000302','O1S000000000303')

结果应该如下

id                    name             transformed_value
O1S000000000301       Screw            Metal Fabrication,Cabinetry/Shelving,Furniture
O1S000000000302       wrench           Carpentry,Handyman
O1S000000000303       impact_driver    Carpentry,Masonry,Handyman

标签： arrayspostgresql

解决方案

您可以WITH在这种情况下使用子句

以这种方式尝试：

with cte(name, value) as (
values
('metal_fabtication_c', 'Metal Fabrication'),
('cabinetery_andor_shelves_c', 'Cabinetry/Shelving'),
('handyman_c', 'Handyman'),
('carpentry_c', 'Carpentry'),
('masonry_c', 'Masonry'),
('table_c', 'Furniture')
),

cte1 as 
(
select id, name, unnest(original_value) as "original_value" from test_table where id in ('O1S000000000301','O1S000000000302','O1S000000000303')
)

select 
t1.id,
t1.name, 
array_agg(t2.value) as "transformed_value"
from cte1 t1 left join cte t2 on t2.name=t1.original_value
group by 1,2

上面是返回转换后的值ARRAY。如果你想要逗号分隔的值，那么你使用string_agg(t2.value,',')而不是array_agg(t2.value)

此外，如果您想要任何新的转换键值对，那么您可以添加第一个WITH块

演示

推荐阅读