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

[hector vass <hector.vass@xxxxxxxxx>]

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@xxxxxxxxx> 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)

```