1
\$\begingroup\$

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": "ajay@xyz.com"},{"Type": "Work", "Email": "ajay@abc.com"}]' 
), (        2, 
            'Vikas', 
            'Singh', 
            '[{"Type": "Mobile", "Number": "8978675612"}, {"Type": "Home", "Number": "21324354"}, {"Type": "Work", "Number": "24256376"}]',
            '[{"Type": "Work", "Email": "vikas@xyz.com"}]'
), (        3, 
            'Atul', 
            'Prasad', 
            '[{"Type": "Mobile", "Number": "7895674563"}]', 
            '[]'
), (        4, 
            'Soumil', 
            'Roy', 
            '[{"Type": "Mobile", "Number": "8798765632"}]', 
            '[{"Type": "Home", "Email": "soumil@xyz.com"}]'
);

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:

enter image description here

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.

\$\endgroup\$
2
\$\begingroup\$

This gets you the same output:

SELECT  id
,   firstname
,   lastname
,   (SELECT string_agg(p->>'Number', ',') FROM jsonb_array_elements(phone) p WHERE p->>'Type' = 'Mobile') AS mobile_phone_number
,   (SELECT string_agg(p->>'Number', ',') FROM jsonb_array_elements(phone) p WHERE p->>'Type' <> 'Mobile') AS unformatted_phone_numbers
,   (SELECT string_agg(e->>'Email', ',') FROM jsonb_array_elements(email) e) AS email_addresses
FROM test.individuals

enter image description here

So basically, rather than creating all the CTEs ahead of time with aggregated results and then joining against them, we just go through the records and compute the results of the last three columns by running some functions on the content of phone/email fields as they're read.

This is the query plan my my version:

Seq Scan on individuals  (cost=0.00..21.22 rows=4 width=111) (actual time=0.114..0.230 rows=4 loops=1)
  SubPlan 1
    ->  Aggregate  (cost=1.51..1.52 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=4)
          ->  Function Scan on jsonb_array_elements p  (cost=0.00..1.50 rows=1 width=32) (actual time=0.013..0.015 rows=1 loops=4)
                Filter: ((value ->> 'Type'::text) = 'Mobile'::text)
                Rows Removed by Filter: 1
  SubPlan 2
    ->  Aggregate  (cost=2.00..2.01 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=4)
          ->  Function Scan on jsonb_array_elements p_1  (cost=0.00..1.50 rows=99 width=32) (actual time=0.008..0.009 rows=1 loops=4)
                Filter: ((value ->> 'Type'::text) <> 'Mobile'::text)
                Rows Removed by Filter: 1
  SubPlan 3
    ->  Aggregate  (cost=1.50..1.51 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=4)
          ->  Function Scan on jsonb_array_elements e  (cost=0.00..1.00 rows=100 width=32) (actual time=0.005..0.005 rows=1 loops=4)
Planning time: 0.297 ms
Execution time: 0.354 ms

And yours:

Hash Left Join  (cost=51.17..56.00 rows=4 width=164) (actual time=0.367..0.386 rows=4 loops=1)
  Hash Cond: (p.id = mob.id)
  CTE persons
    ->  Seq Scan on individuals  (cost=0.00..1.04 rows=4 width=15) (actual time=0.032..0.034 rows=4 loops=1)
  CTE email_ids
    ->  Seq Scan on individuals individuals_1  (cost=0.00..3.03 rows=400 width=36) (actual time=0.034..0.056 rows=4 loops=1)
  CTE email_aggregated
    ->  HashAggregate  (cost=11.00..13.50 rows=200 width=36) (actual time=0.088..0.091 rows=3 loops=1)
          Group Key: email_ids.id
          ->  CTE Scan on email_ids  (cost=0.00..8.00 rows=400 width=36) (actual time=0.037..0.061 rows=4 loops=1)
  CTE phone_numbers
    ->  Seq Scan on individuals individuals_2  (cost=0.00..3.03 rows=400 width=36) (actual time=0.030..0.048 rows=8 loops=1)
  CTE mobile_numbers
    ->  GroupAggregate  (cost=10.01..10.06 rows=2 width=36) (actual time=0.033..0.041 rows=4 loops=1)
          Group Key: phone_numbers.id
          ->  Sort  (cost=10.01..10.02 rows=2 width=36) (actual time=0.025..0.027 rows=4 loops=1)
                Sort Key: phone_numbers.id
                Sort Method: quicksort  Memory: 25kB
                ->  CTE Scan on phone_numbers  (cost=0.00..10.00 rows=2 width=36) (actual time=0.004..0.012 rows=4 loops=1)
                      Filter: ((phone_object ->> 'Type'::text) = 'Mobile'::text)
                      Rows Removed by Filter: 4
  CTE other_numbers
    ->  HashAggregate  (cost=12.98..15.48 rows=200 width=36) (actual time=0.084..0.086 rows=2 loops=1)
          Group Key: phone_numbers_1.id
          ->  CTE Scan on phone_numbers phone_numbers_1  (cost=0.00..10.00 rows=398 width=36) (actual time=0.042..0.069 rows=4 loops=1)
                Filter: ((phone_object ->> 'Type'::text) <> 'Mobile'::text)
                Rows Removed by Filter: 4
  ->  Hash Right Join  (cost=4.97..9.76 rows=4 width=132) (actual time=0.298..0.315 rows=4 loops=1)
        Hash Cond: (oth.id = p.id)
        ->  CTE Scan on other_numbers oth  (cost=0.00..4.00 rows=200 width=36) (actual time=0.085..0.088 rows=2 loops=1)
        ->  Hash  (cost=4.92..4.92 rows=4 width=100) (actual time=0.193..0.193 rows=4 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Hash Right Join  (cost=0.13..4.92 rows=4 width=100) (actual time=0.167..0.185 rows=4 loops=1)
                    Hash Cond: (eml.id = p.id)
                    ->  CTE Scan on email_aggregated eml  (cost=0.00..4.00 rows=200 width=36) (actual time=0.090..0.096 rows=3 loops=1)
                    ->  Hash  (cost=0.08..0.08 rows=4 width=68) (actual time=0.059..0.059 rows=4 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  CTE Scan on persons p  (cost=0.00..0.08 rows=4 width=68) (actual time=0.040..0.050 rows=4 loops=1)
  ->  Hash  (cost=0.04..0.04 rows=2 width=36) (actual time=0.052..0.052 rows=4 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  CTE Scan on mobile_numbers mob  (cost=0.00..0.04 rows=2 width=36) (actual time=0.036..0.048 rows=4 loops=1)
Planning time: 0.719 ms
Execution time: 0.693 ms

Neither are particularly performant, although my version appears about twice as fast, but the stats can't be trusted with so little data, so it's not very useful right now.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.