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.

• user1822: 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: 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.
• ceadreak: @a_horse_with_no_name I created a snippet with a part of the function gist.github.com/ceadreak/eeb9594af3a17e1d18c7281a17fdf2fe
• ceadreak: @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

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

• ceadreak: @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: 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 ?
• Erwin Brandstetter: @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.