Flatten JSON to get relevant data in Postgresql
This gets you the same output:

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




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
    -&gt;  Aggregate  (cost=1.51..1.52 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=4)
          -&gt;  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 -&gt;&gt; 'Type'::text) = 'Mobile'::text)
                Rows Removed by Filter: 1
  SubPlan 2
    -&gt;  Aggregate  (cost=2.00..2.01 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=4)
          -&gt;  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 -&gt;&gt; 'Type'::text) &lt;&gt; 'Mobile'::text)
                Rows Removed by Filter: 1
  SubPlan 3
    -&gt;  Aggregate  (cost=1.50..1.51 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=4)
          -&gt;  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
    -&gt;  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
    -&gt;  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
    -&gt;  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
          -&gt;  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
    -&gt;  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
    -&gt;  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
          -&gt;  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
                -&gt;  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 -&gt;&gt; 'Type'::text) = 'Mobile'::text)
                      Rows Removed by Filter: 4
  CTE other_numbers
    -&gt;  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
          -&gt;  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 -&gt;&gt; 'Type'::text) &lt;&gt; 'Mobile'::text)
                Rows Removed by Filter: 4
  -&gt;  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)
        -&gt;  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)
        -&gt;  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
              -&gt;  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)
                    -&gt;  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)
                    -&gt;  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
                          -&gt;  CTE Scan on persons p  (cost=0.00..0.08 rows=4 width=68) (actual time=0.040..0.050 rows=4 loops=1)
  -&gt;  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
        -&gt;  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.