postgresql: mapping json array to string array

Question

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"
]

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