0

I have following PostgreSql function:

CREATE OR REPLACE FUNCTION "fn_get_list_invoice_number_by_product_ids"(_product_ids INT[])



RETURNS "pg_catalog"."json" AS $BODY$
    
BEGIN   
    RETURN (SELECT json_agg((record)) FROM 
                        (
                                SELECT DISTINCT invoice_number FROM sale.invoice_item 
                                WHERE product_id = ANY(_product_ids)
                        ) record
                    );
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

After using this function I get following result:

[{"invoice_number":"POS20-000043A"}, 
 {"invoice_number":"POS20-000002E"}, 
 {"invoice_number":"POS20-000009E"}, 
 {"invoice_number":"POS20-000003E"}, 
 {"invoice_number":"POS20-000046A"}]

But if I want result as below, what should I do?

[
      "POS20-000043A",
      "POS20-000002E",
      "POS20-000009E",
      "POS20-000003E",
      "POS20-000046A"
]
1

Don't aggregate the whole row, just aggregate the column:

SELECT json_agg(invoice_number) 
FROM 
(
  SELECT DISTINCT invoice_number 
  FROM sale.invoice_item 
  WHERE product_id = ANY(_product_ids)
) record

Or even simpler:

SELECT json_agg(DISTINCT invoice_number)
FROM sale.invoice_item 
WHERE product_id = ANY(_product_ids)
1
  • Wow, thanks for your answer! This is great! It works now. Aug 20 '20 at 8:10

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.