Concat Multiple columns into one-CodePudding

I would like to concat multiple columns into a single column. When a value is Null I wish not to show it but I do want to show the values that exist. I couldn't find an example stackoverflow, below is a table of result set I'm trying to get, however, in this case because the "email" is null the entire result set is null not sure why this is occurring. I apologize in advance for my table if it doesn't display properly I've been using senseful github to create my table(s) but when I copy and paste here it displays weird.

SELECT
    Id,
    'study_id:' || study_id ||' , ' || 'email:' || email ||' , ' || 'phone:' || phone AS "Longvalue",
FROM
    mv_itest mi

id	Longvalue
123	`study_id:123 , phone:123-123-1234`

This is the result set if I use concat, however, I do not wish to display the alias for phone if the value is null

----- ----------------------- -- | id | longvalue | | ----- ----------------------- -- | 987 | study_id:456 , phone: | | ----- ----------------------- --

CodePudding user response：

Since you want something like a json result, you might do that using JSON functions (it is already explained concatting NULL would yield null):

select id,
       regexp_replace(
               jsonb_strip_nulls(
                       jsonb_build_object(
                               'study_id:', study_id,
                               'email:', email,
                               'phone:', phone
                           ))::text, '[{}"]', '', 'g') as "longvalue"
from mv_itest mi;