0

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?

0

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
| improve this answer | |
  • Very close! Unfortunately, it's returning all the records (not adhering to the 6 limit) and it's returning the records in this format: [{ plan 1: 22, plan 2: 12 ...etc]}, when it should be [{ planname: 'plan 1', subscribers: '12' },{ planname: 'plan 2': subscribers: '12'} ...etc ]` – Matt Carlotta Sep 12 '18 at 2:41
  • I was able to fix the naming convention, but the limit still doesn't work. The fix for the naming was: jsonb_build_object('plan', planName, 'count', subscribers) – Matt Carlotta Sep 12 '18 at 2:56
  • @MFC88 see the update – Evan Carroll Sep 12 '18 at 4:08
  • Very close again, but the order was incorrect due to the ORDER BY being in the statement. However, I was able to make your solution work with a bit of tweaking. See my answer. – Matt Carlotta Sep 12 '18 at 4:14
  • Typo: The ORDER BY was in the wrong statement. I'll be using your modified answer since it seems the most efficient. Thank you for your help!!! It is greatly appreciated! – Matt Carlotta Sep 12 '18 at 4:22
0

With the help and guidance of @Evan Carroll pointing me in the right direction, I was able to work out two solutions:

CROSS JOIN (

  // gathers and filters plans by userid and status
  WITH filtered_plans_table AS (
    SELECT planName,subscribers 
    FROM plans 
    WHERE userid=$1 AND status='active'
  )

  // aggregates and returns a "popularplans" JSON object from the "top_6_plans" below
  SELECT json_agg(filtered_plans) as popularplans 

  // reorders and limits the "filtered_table_plans" to 6 records
  FROM (
    SELECT * 
    FROM filtered_plans_table 
    ORDER BY subscribers DESC 
    LIMIT 6
  ) 
  AS top_6_plans 

) t

OR

CROSS JOIN (
  // aggregates and returns a "popularplans" JSON object from the "filtered_plans_table" below
  SELECT json_agg(filtered_plans_table) as popularplans

  // filters, reorders and limits to 6 records
  FROM (
    SELECT planName,subscribers 
    FROM plans WHERE userid=$1 AND status='active' 
    ORDER BY subscribers DESC 
    LIMIT 6
  ) as filtered_plans_table

) popularplans

Result:

 [ 
   {
     subscribers: '146',
     inactivesubscribers: '12',
     plans: '12',
     popularplans: [ 
       { planname: 'Plan 1', subscribers: '22' },
       { planname: 'Plan 2', subscribers: '12' }, 
       { planname: 'Plan 3', subscribers: '11' }, 
       { planname: 'Plan 4', subscribers: '10' }, 
       { planname: 'Plan 5', subscribers: '8' }, 
       { planname: 'Plan 6', subscribers: '6' }, 
     ],
     promotionals: '12',
     charges: '7',
     refunds: '12',
     messages: '5',
     activetemplates: '4',
     inactivetemplates: '4' 
  } 
]
| improve this answer | |

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.