Re: Get back the number of columns of a result-set prior to JSON aggregation

I think you are just trying to get the number of columns in the underlying
table, no real cost to read the metadata

select count(id), (select count(attrelid) from pg_attribute where attrelid=
't1'::regclass and attnum>0) , json_agg(t) from t1 t;

select count(id), (select count(attrelid) from pg_attribute where attrelid=
't2'::regclass and attnum>0) , json_agg(t) from t2 t;

Regards
Hector Vass
07773 352559

On Tue, Nov 28, 2023 at 12:12 PM Dominique Devienne <ddevienne(at)gmail(dot)com>
wrote:

> Hi. I've got a nice little POC using PostgreSQL to implement a REST API
> server.
> This uses json_agg(t) to generate the JSON of tables (or subqueries in
> general),
> which means I always get back a single row (and column, before I added the
> count(t.*)).
>
> But I'd like to get statistics on the number of rows aggregated (easy,
> count(*)),
> but also the number of columns of those rows! And I'm stuck for the
> latter...
>
> Is there a (hopefully efficient) way to get back the cardinality of a
> select-clause basically?
> Obviously programmatically I can get the row and column count from the
> result-set,
> but I see the result of json_agg() myself, while I want the value prior to
> json_agg().
>
> Is there a way to achieve this?
>
> Thanks, --DD
>
> PS: In the example below, would return 1 for the 1st query, and 2 for the
> 2nd.
>
> ```
> migrated=> create table t1 (id integer);
> CREATE TABLE
> migrated=> insert into t1 values (1), (2);
> INSERT 0 2
> migrated=> create table t2 (id integer, name text);
> CREATE TABLE
> migrated=> insert into t2 values (1, 'one'), (2, 'two');
> INSERT 0 2
> migrated=> select count(t.*), json_agg(t) from t1 t;
> count | json_agg
> -------+-------------
> 2 | [{"id":1}, +
> | {"id":2}]
> (1 row)
>
>
> migrated=> select count(t.*), json_agg(t) from t2 t;
> count | json_agg
> -------+--------------------------
> 2 | [{"id":1,"name":"one"}, +
> | {"id":2,"name":"two"}]
> (1 row)
> ```
>