0

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
Improve this question
1
  • So where does 'Metal Fabrication' come from? Commented Aug 7, 2020 at 6:08

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@Akilesh Mishra Thanks very much, your solution was helpful.
1

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

Improve this answer

7 Comments

Thanks J.Sperandino, Second Data is not a table, I tried a Case statement, SELECT CASE WHEN (SELECT UNNEST(ARRAY[original_value])) = 'metal_fabrication_c' THEN 'Metal Fabrication' END AS test_Array FROM test_table WHERE id = 'O1S000000000301'; I get an** " ERROR: more than one row returned by a subquery used as an expression" **
You can't load the second data into a temp table to accomplish the desired situation ? If not , what is the second data ?
How can I create a Temp table in a SQL query, Sorry I thought if i have to go that route then this should be Procedure of Function in PostgreSQL, sorry new to PostgreSQL
First aswering the first comment: The problem behind your try is unnest function return more than one row , and you are comparing with one unique value, to work with array comparation you can user the word ANY check this stackoverflow.com/questions/11231544/…
Temporary tables exist in the context of the execution of your session, when finished it will be deleted, to create one just add the word TEMPORARY or TEMP after CREATE [TEMP / TEMPORARY] TABLE
|
0

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;
Improve this answer

1 Comment

Thanks I thought the same, most of the times the Transformed Value follow the Logic, but at times The transformed value will different Say here (Table__c is transformed as FURNITURE and etc)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.