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

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