Resultset from jsonb array

Question

I have a function that build a JSONB array of object.

CREATE OR REPLACE FUNCTION my_func()
  RETURNS TABLE(field1 INT, field2 TEXT)

In DECLARE part:

DECLARE
    r RECORD;
    d JSONB;
    d_list JSONB[];
    ...

Then, I loop on a query result to build a d JSON object

FOR r IN
    SELECT ........

LOOP    
    d = jsonb_build_object('field1', r.foo, 'field2', r.bar);
    d_list = d_list || d;
END LOOP;

And I want to return a recordset of d_list

RETURN QUERY (
  select * from jsonb_to_recordset(d_list) as x(field1 int, field2 text)  
);

This does not work because jsonb_to_recordset cannot handle JSONB[]

How can I achieve that ? I read about UNNEST() but I don't understand how to use it in this case.

Answer 1

The confusion with JSON array (type json or jsonb) versus Postgres array of JSON (type json[] or jsonb[]) is a red herring in your case. Looking at the query you disclosed in a later comment, all of this is pointless complication. There is no need to involve JSON at all, nor all the casting and concatenation, nor even a loop.

Radically simplify to a plain SQL function:

CREATE OR REPLACE FUNCTION custom_deals(_deals_count int DEFAULT 6)
  RETURNS TABLE(target_id int, target_type text, weight int)
  LANGUAGE sql ROWS 10 AS
$func$
SELECT *, row_number() OVER (ORDER  BY random())::int  AS rn  -- returned as weight!
FROM  (
   (
   SELECT p.id AS target_id, 'product' AS target_type
   FROM   products p
   JOIN   products_configurations pc ON p.id = pc.product_id
   JOIN   content_products        cp ON p.id = cp.product_id
   WHERE  pc.deleted_at IS NULL
   AND    (pc.status & 1) = 0    -- faster than the cast you had
   AND    (cp.status & 16) = 16  -- published
   AND    (p.status & 3) = 3     -- active and available
   AND    p.recommendation_rate = 3
   ORDER  BY random()
   LIMIT  10
   )
   UNION
   (
   SELECT d.id, 'discount'
   FROM   discounts                d
   JOIN   discounts_configurations dc ON d.id = dc.discount_id
   JOIN   content_discounts        cd ON d.id = cd.discount_id
   WHERE  (dc.status & 1) = 0
   AND    (cd.status & 16) = 16  -- published
   AND    (d.status & 1) = 0     -- not suspended
   AND    d.recommendation_rate = 3
   ORDER  BY random()
   LIMIT  10
   )
   ) dp
ORDER  BY rn
LIMIT  _deals_count;
$func$;

There can be no duplicates between the two legs of the UNION. But the table name products_configurations indicates a many-to-one relationship to products. If so the [INNER] JOIN can multiply rows within each leg, and UNION is probably there to remove those duplicates. The manual:

Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

That's a very costly way of doing things. I suggest to fix that with EXISTS, which does not multiply rows, so you don't have to remove duplicates later, and a faster UNION ALL does the job:

CREATE OR REPLACE FUNCTION custom_deals(_deals_count int DEFAULT 6)
  RETURNS TABLE(target_id int, target_type text, weight int)
  LANGUAGE sql ROWS 10 AS
$func$
SELECT *, row_number() OVER (ORDER  BY random())::int  AS rn   -- returned as weight!
FROM  (
   (
   SELECT p.id AS target_id, 'product' AS target_type
   FROM   products p
   WHERE (p.status & 3) =  3        -- active and available
   AND    p.recommendation_rate = 3
   AND    EXISTS (  -- !
      SELECT FROM products_configurations pc
      WHERE  pc.product_id = p.id
      AND    pc.deleted_at IS NULL
      AND   (pc.status & 1) = 0     -- faster than the cast you had
      )
   AND    EXISTS (  -- !
      SELECT FROM content_products cp
      WHERE  cp.product_id =  p.id
      AND   (cp.status & 16) = 16   -- published
      )
   ORDER  BY random()
   LIMIT  10
   )
   UNION ALL  -- !!
   (
   SELECT d.id, 'discount'
   FROM   discounts d
   WHERE  d.recommendation_rate = 3      
   AND   (d.status  &  1) =  0      -- not suspended
   AND    EXISTS (  -- !
      SELECT FROM discounts_configurations dc
      WHERE  dc.discount_id = d.id
      AND   (dc.status &  1) =  0
      )
   AND    EXISTS (  -- !
      SELECT FROM content_discounts cd
      WHERE  cd.discount_id = d.id
      AND   (cd.status & 16) = 16  -- published
      )    
   ORDER  BY random()
   LIMIT  10
   )
   ) dp
ORDER  BY rn
LIMIT  _deals_count;
$func$;

At this stage, the function should be faster by orders of magnitude. (Besides actually working.)