Following is my table:
CREATE TABLE test.individuals
(
id INT PRIMARY KEY,
firstname VARCHAR,
lastname VARCHAR,
phone JSONB,
email JSONB
);
Which contains the following records (say):
INSERT INTO test.individuals (
id,
firstname,
lastname,
phone,
email
) VALUES ( 1,
'Ajay',
'Upadhaya',
'[{"Type": "Mobile", "Number": "9876543210"}, {"Type": "Home", "Number": "23456789"}, {"Type": "Work", "Number": "24356758"}]',
'[{"Type": "Home", "Email": "[email protected]"},{"Type": "Work", "Email": "[email protected]"}]'
), ( 2,
'Vikas',
'Singh',
'[{"Type": "Mobile", "Number": "8978675612"}, {"Type": "Home", "Number": "21324354"}, {"Type": "Work", "Number": "24256376"}]',
'[{"Type": "Work", "Email": "[email protected]"}]'
), ( 3,
'Atul',
'Prasad',
'[{"Type": "Mobile", "Number": "7895674563"}]',
'[]'
), ( 4,
'Soumil',
'Roy',
'[{"Type": "Mobile", "Number": "8798765632"}]',
'[{"Type": "Home", "Email": "[email protected]"}]'
);
My requirement was to get mobile numbers in one field, and the rest of the numbers in another field (Since, there can be multiple numbers, they have to be separated by commas). Same for email ids.
Following is my query:
WITH persons
AS (SELECT id,
firstname,
lastname
FROM test.individuals),
email_ids
AS (SELECT id,
Jsonb_array_elements(email) :: jsonb AS email_object
FROM test.individuals),
email_aggregated
AS (SELECT id,
String_agg(email_object ->> 'Email', ',') AS email_id
FROM email_ids
GROUP BY id),
phone_numbers
AS (SELECT id,
Jsonb_array_elements(phone) :: jsonb phone_object
FROM test.individuals),
mobile_numbers
AS (SELECT id,
String_agg(phone_object ->> 'Number', ',') AS mobile_number
FROM phone_numbers
WHERE phone_object ->> 'Type' = 'Mobile'
GROUP BY id),
other_numbers
AS (SELECT id,
String_agg(phone_object ->> 'Number', ',') AS phone_number
FROM phone_numbers
WHERE phone_object ->> 'Type' <> 'Mobile'
GROUP BY id)
SELECT p.*,
mob.mobile_number mobile_phone_number,
oth.phone_number unformatted_phone_numbers,
eml.email_id email_addresses
FROM persons p
left join email_aggregated eml
ON p.id = eml.id
left join other_numbers oth
ON p.id = oth.id
left join mobile_numbers mob
ON p.id = mob.id
This gives me the required result:
I came up with this query intuitively. I don't know if there is a better way to do this. Any comments will be appreciated.
