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

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Get back the number of columns of a result-set prior to JSON aggregation
Date: 2023-11-28 12:12:20
Message-ID: CAFCRh-8tyng7SzUea8KoNsOgSf667un1GN3rJTsP5xXT_0tDug@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)
```

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hector vass 2023-11-28 14:14:05 Re: How to eliminate extra "NOT EXISTS"-query here?
Previous Message Kyotaro Horiguchi 2023-11-28 08:54:43 Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.