Postgres: Return json clob

Question

First of all I have in Oracle a procedure which returns a clob. This clob contains a json string which I created with pljson from a sql-select. Like this:

procedure xyz
(
    o_json out clob
)
is
    m_json_list json_list := json_list;
    m_json_temp json;
begin
    for cs in (select id, name, birthday from profile)loop
        m_json_temp := json;

        m_json_temp.put('id', cs.id);
        m_json_temp.put('name', cs.name);
        m_json_temp.put('birthday', cs.birthday);

        m_json_list.add(m_json_temp);
    end loop;
    o_json := convertToClob(m_json_list);
end xyz;

Now I want to achive the same result with a Postgres database. The only why I found is that I have a table with one cloumn which has the type 'json' and contains the whole json. This is not what I am searching for.

Can somebody give me an example how to achive this scenario in postgresql?

EDIT: Here is an example for an inner join:

procedure xyz
(
    o_json out clob
)
is
    m_json_list json_list := json_list;
    m_json_temp json;
begin
    for cs in (select ppf.id, ppf.name, ppf.birthday, ott.info from profile ppf inner join other_table ott on ott.ott_id = ppf.id )loop
        m_json_temp := json;

        m_json_temp.put('id', cs.id);
        m_json_temp.put('name', cs.name);
        m_json_temp.put('birthday', cs.birthday);
        m_json_temp.put('info', cs.info);

        m_json_list.add(m_json_temp);
    end loop;
    o_json := convertToClob(m_json_list);
end xyz;

Answer 1

So, you are looking for a way to construct a json array from a query.

Given a table and some test data:

postgres=# create table profile(id serial, name text, birthday date);
CREATE TABLE
postgres=# insert into profile(name, birthday) values('John', current_date - interval '30 years');
INSERT 0 1
postgres=# insert into profile(name, birthday) values('Jack', current_date - interval '25 years');
INSERT 0 1

You can represent rows as json objects like this:

postgres=# select row_to_json(p.*) from profile p;
                  row_to_json                   
------------------------------------------------
 {"id":1,"name":"John","birthday":"1986-03-29"}
 {"id":2,"name":"Jack","birthday":"1991-03-29"}
(2 rows)

And then aggregate these json objects into an array:

postgres=# select json_agg(row_to_json(p.*)) from profile p;
                                             json_agg                                             
--------------------------------------------------------------------------------------------------
 [{"id":1,"name":"John","birthday":"1986-03-29"}, {"id":2,"name":"Jack","birthday":"1991-03-29"}]
(1 row)

Even more simpler, you can just use an aggregation and it will do all the conversions for you:

postgres=# select json_agg(p.*) from profile p;
                     json_agg                      
---------------------------------------------------
 [{"id":1,"name":"John","birthday":"1986-03-29"}, +
  {"id":2,"name":"Jack","birthday":"1991-03-29"}]
(1 row)

(Never mind + sign, it is not a part of json.)