Postgresql – How to combine a large CROSS JOIN query and return 1 result

postgresql

I have a rather large PostgresSQL query that returns a single JSON result that is then sent to the front-end app for display:

Select * FROM (SELECT sum(subscribers) AS subscribers FROM plans WHERE userid=$1 AND startDate>=$2 AND startDate<$3) subscribers
                CROSS JOIN (SELECT count(status) AS inactivesubscribers FROM subscribers WHERE userid=$1 AND status='inactive' OR status='suspended') inactivesubs
                CROSS JOIN (SELECT count(planName) AS plans FROM plans WHERE userid=$1 AND status='active') plans
                CROSS JOIN (SELECT count(promoCode) AS promotionals FROM promotionals WHERE userid=$1 AND status='active' AND startDate>=$2 AND startDate<$3) promotionals
                CROSS JOIN (SELECT count(chargeDate) AS charges FROM transactions WHERE userid=$1 AND chargeDate>=$2 AND chargeDate<$3) charges
                CROSS JOIN (SELECT count(refundDate) AS refunds FROM transactions WHERE userid=$1 AND refundDate>=$2 AND refundDate<$3) refunds
                CROSS JOIN (SELECT count(sentDate) AS messages FROM messages WHERE userid=$1 AND sentDate>=$2 AND sentDate<$3) messages
                CROSS JOIN (SELECT count(templateName) AS activetemplates FROM templates WHERE userid=$1 AND status='active') activetemplates
                CROSS JOIN (SELECT count(templateName) AS inactivetemplates FROM templates WHERE userid=$1 AND status='suspended') inactivetemplates

Returns:

[
  {
    subscribers: '146',
    inactivesubscribers: '12',
    plans: '12',
    promotionals: '12',
    charges: '7',
    refunds: '12',
    messages: '5',
    activetemplates: '4',
    inactivetemplates: '4' 
  }
]

But, I'm not sure how I can add this query to gather 6 of the most popular plans within the large query:

CROSS JOIN (SELECT planName,subscribers as popularplans FROM plans WHERE userid=$1 AND status='active' ORDER BY subscribers DESC LIMIT 6) popularplans

Due to the CROSS JOIN instructions, the query above doesn't work as intended, because the query creates 6 different object results with planname: plan x and popularplans: x on each found row.

[
      {
        subscribers: '146',
        inactivesubscribers: '12',
        planname: 'Plan 1',
        plans: '12',
        popularplans: '22',
        promotionals: '12',
        charges: '7',
        refunds: '12',
        messages: '5',
        activetemplates: '4',
        inactivetemplates: '4' 
     },
     {
        subscribers: '146',
        inactivesubscribers: '12',
        planname: 'Plan 2',
        plans: '12',
        popularplans: '12',
        promotionals: '12',
        charges: '7',
        refunds: '12',
        messages: '5',
        activetemplates: '4',
        inactivetemplates: '4' 
     }
     ...etc
 ]

Ideally, I want the large query to return a single JSON object and be structured like so:

[
  {
    subscribers: '146',
    inactivesubscribers: '12',
    plans: '12',
    popularplans: [
      { planName: 'Plan 1', subscribers: '22' },
      { planName: 'Plan 2', subscribers: '12' },
      ...etc
    ],
    promotionals: '12',
    charges: '7',
    refunds: '12',
    messages: '5',
    activetemplates: '4',
    inactivetemplates: '4' 
  }
]

Is there a better or a different approach to the large query than tons of cross joins?

Best Answer

Try this using jsonb_object_agg(), and jsonb_build_object(k,v)

CROSS JOIN (
  SELECT json_agg(jsonb_build_object('plan', planName, 'count', subscribers) ORDER BY subscribers DESC) as popularplans 
  FROM ( 
    SELECT *
    FROM plans
    WHERE userid=$1 AND status='active'
    FETCH FIRST 6 ROWS ONLY
  )
) popularplans
Related Question