PL/pgSQL rows to json array

Question

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!

ravioli · Accepted Answer · 2018-03-20 16:24:11Z

6

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

answered Mar 20, 2018 at 16:24

ravioli

3,8213 gold badges17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matteo M. Over a year ago

Thanks for the hint! For future references the working fuction is

CREATE OR REPLACE FUNCTION get_users_list()  RETURNS JSON as $$ DECLARE  users JSON; BEGIN  SELECT json_agg(src) INTO users  FROM (  SELECT users.id, users.name,         users.surname, roles.role,          users.username  		FROM users, roles 		WHERE users.fkrole = roles.id 	) src  ;   RETURN users; END; $$ LANGUAGE plpgsql;

ravioli Over a year ago

My pleasure. :)

ravioli Over a year ago

You got it my friend :)

Collectives™ on Stack Overflow

PL/pgSQL rows to json array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related