Get back the number of columns of a result-set prior to JSON aggregation
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)
```
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@gmail.com>
wrote:
Show quoted text
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)
```
On 2023-11-28 13:12 +0100, Dominique Devienne 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?
You can access the first array element and count the number of object
keys. But this only works with non-empty arrays.
test=# select count(*) from jsonb_object_keys('[{"id":1,"name":"one"},{"id":2,"name":"two"}]'::jsonb->0);
count
-------
2
(1 row)
--
Erik