CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Matteo M.
Matteo M.

Reputation: 107

PL/pgSQL rows to json array

I'm trying to retrieve a json array containing the rows returned with this function:

CREATE OR REPLACE FUNCTION get_users_list() 
RETURNS TABLE (
id INTEGER,
name VARCHAR,
surname VARCHAR,
fkrole INTEGER,
username VARCHAR
) as $$
BEGIN

RETURN QUERY SELECT users.id, users.name,
                    users.surname, users.fkrole, 
                    users.username 
FROM users;
END;
$$ LANGUAGE plpgsql;

Can anyone give me a hint? How can I convert multiple rows into a single JSON array?

Thanks!

Upvotes: 2

Views: 8211

Answers (1)

ravioli
ravioli

Reputation: 3833

Give this a try:

SELECT JSON_AGG(src) AS my_json_array
FROM (
  SELECT 
    users.id, 
    users.name, 
    users.surname, 
    users.fkrole, 
    users.username 
  FROM users
) src
;

This will give you all the rows from your "src" query returned as a single json array.

http://www.sqlfiddle.com/#!17/6241a/2

Here's some more info on the Postgres JSON functions: https://www.postgresql.org/docs/10/static/functions-json.html

Upvotes: 6

Related Questions