PostgreSQL Performance: Solving Query Issues with array_to_json()

jsonpostgresqlpostgresql-performance

Considering my basic knowledge of PostgreSQL I am unable to find out why my second statement for where clause (record->'details'->>'amount')::numeric(19,2) = 2.99 is not working with current query. Idea is to send common filter from UI for header and details and when I am trying to filter to inner array details Json column its not giving me a exact result though it has some data within it

WITH t as (select row_to_json(rr)::jsonb as record
from (
       select (
                select row_to_json(row)
                from (
                       select 
                             c.customer_id,
                             c.first_name,
                             c.last_name,
                             c.email,                   
                             count(*) total, 
                             sum(p2.amount) total_amount
                       from payment p2
                       where p2.customer_id = c.customer_id
                     ) row
              ) as header,
              (select array_to_json(array_agg(row_to_json(row)))
                from (
                       select
                             amount,
                             payment_date                              
                       from payment p1                            
                       where p1.customer_id = c.customer_id
                     ) row
              ) as details
       from customer c
       join payment p on p.customer_id = c.customer_id
     ) rr) 

    SELECT record FROM t 
    where record->'header'->>'customer_id'= '2'
    and (record->'details'->>'amount')::numeric(19,2)= 2.99

Best Answer

It would be performance suicide to first build a huge jsonb object from joining two tables, and then filter a single record representing an underlying row. You would filter first, of course.

Going out on a limb, this might be what you are trying to do:

SELECT to_jsonb(rr) AS record
FROM  (
   SELECT (SELECT to_jsonb(row) FROM  (
              SELECT c.customer_id
                   , c.first_name
                   , c.last_name
                   , c.email                
                   , p3.total
                   , p3.total_amount) h) AS header
        , p3.details
   FROM   customer c
   JOIN   LATERAL (
      SELECT count(*) AS total
           , sum(p2.amount) AS total_amount
           , jsonb_agg(detail) AS details
      FROM  (
         SELECT p.amount
              , (SELECT to_jsonb(d) FROM (SELECT p.amount, p.payment_date) d) AS detail
         FROM   payment p
         WHERE  p.customer_id = c.customer_id
         ) p2
      ) p3 ON p3.total_amount = 2.99   -- here !
   WHERE  customer_id = 2              -- and here !
   ) rr

There is no record->'details'->>'amount' in your object. There are array elements like record->'details'->0->>'amount', but those make no sense with the = operator. So I assumed you are aiming for total_amount.

A little less confusing with jsonb_build_object():

SELECT to_jsonb(rr) AS record
FROM  (
   SELECT jsonb_build_object('customer_id', c.customer_id
                           , 'first_name', c.first_name
                           , 'last_name', c.last_name
                           , 'email', c.email                
                           , 'total', p3.total
                           , 'total_amount', p3.total_amount) AS header
        , p3.details
   FROM   customer c
   JOIN   LATERAL (
      SELECT count(*) AS total
           , sum(p2.amount) AS total_amount
           , jsonb_agg(detail) AS details
      FROM  (
         SELECT p.amount
              , jsonb_build_object('amount', p.amount
                                 , 'payment_date', p.payment_date) AS detail
         FROM   payment p
         WHERE  p.customer_id = c.customer_id
         ) p2
      ) p3 ON p3.total_amount = 2.99   -- here !
   WHERE  customer_id = 2              -- and here !
   ) rr;
Related Question