CODEWITHSUNDEEP
arrayspostgresql
vasanthanand
vasanthanand

Reputation: 13

Transform Value in a Column(array Datatype) based on the Array list's Value in a PostgreSQL Query

I need your help on Postgresql query logic

Say I have a Table

Environment Info: AWS Aurora Postgresql db 10.6.x

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

Table with values

 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 is not a table but it has Name and its corresponding Transformation as below

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

I have to Write a Query in which Original_value should get this equivalent transformed_Value.

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

The result should be the following

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

Upvotes: 0

Views: 451

Answers (3)

user330315
user330315

Reputation:

It seems you simply want to replace _ with underscored, make every first word upper case and remove the trailing _c. If that is true (and fabtication is a type), you can use a combination of replace, regexp_replaceandinitcap()`

select id, name, 
       string_agg(initcap(replace(regexp_replace(ov, '_c$', ''), '_', ' ')), ',' order by x.idx) as value
from test_table t
   cross join unnest (original_value) with ordinality as x(ov,idx)
where id IN ('O1S000000000301','O1S000000000302','O1S000000000303')   
group by id, name;

Upvotes: 0

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

You can use WITH clause for this scenario

try in this way:

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

Above is returning transformed value in ARRAY. if you want comma separated values then you use string_agg(t2.value,',') instead of array_agg(t2.value)

Also if you want any new transformed key value pair then you can add in first WITH Block

DEMO

Upvotes: 2

J.Sperandio
J.Sperandio

Reputation: 117

I assume this second data "Transformed_value " as a table, and fixing some mistakes that I believe to be typo (metal_fabrication_c/metal_fabtication_c).

It is possible to break the array with the unnest(anyarray), and reference the data with the transformed values ​​table, and then aggregate the data again with string_agg(expression, delimiter), thus arriving at what I believe to be your need.

with reference as (
select 
    tt.id,tt.name,unnest(tt.original_value) as original 
from test_table tt
)
select 
    r.id,
    r.name,
    string_agg(r.original,',') as original,
    string_agg(tv.value,',') as transformed 
from reference r
left join transformed_value tv on tv.name = r.original
group by r.id,r.name

enter image description here

Complete code you can check here: DBFiddle

Upvotes: 1

Related Questions