0

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.

4
  • That doesn't look like you need a loop or even PL/pgSQL but without seeing the complete code this is impossible to answer.
    – user1822
    Commented Mar 31, 2023 at 15:35
  • 2
    jsonb[] is not a JSON array it's an array of JSON. As it is a "native" array you need to use array functions to work with it, not JSON function. In this case you need unnest() to turn array elements into rows.
    – user1822
    Commented Mar 31, 2023 at 15:37
  • @a_horse_with_no_name I created a snippet with a part of the function gist.github.com/ceadreak/eeb9594af3a17e1d18c7281a17fdf2fe
    – ceadreak
    Commented Mar 31, 2023 at 16:52
  • @a_horse_with_no_name as you can see in the snippet, the expected result should be a table containing data for each jsonb objects in the array. Any idea of how to achieve that ? Thank you
    – ceadreak
    Commented Apr 1, 2023 at 12:11

1 Answer 1

1
+50

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.)

3
  • @Erwinn Brandstetter, Thanks for the answer. I'll check and test that today. Why do you say that 'UNION made no sense' and use UNION ALL instead ?
    – ceadreak
    Commented May 3, 2023 at 10:11
  • And what about the contains notion if several binary values are set ? e.g. cp.status = 17 (published and another...). I have to use (cp.status & 16)::BOOL no ?
    – ceadreak
    Commented May 3, 2023 at 11:23
  • @ceadreak: You never need to cast for this. Especially not when ANDing with a power of 2 (16 = 2^4), where the result is either 0 or the same power of 2. To catch any matching bit for other numbers or generally, make it (cp.status & 123) > 0. That's really a distinct matter. Ask a new question if anything is unclear. Commented May 3, 2023 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.