I want a way to dynamically aggregate arbitrary query results in json format (with camelCase keys).
The jsonb_agg function is really close to what I want.
--Example table
create table test.foo(id int, some_col text, another_val date);
insert into test.foo(id, some_col, another_val)
values (1, 'Hello there', '2020-01-01'),
(2, 'Bonjour', '1984-02-23');
--select
select jsonb_agg(f)
from test.foo f;
which returns
[{"id": 1, "some_col": "Hello there", "another_val": "2020-01-01"}, {"id": 2, "some_col": "Bonjour", "another_val": "1984-02-23"}]
This is almost what I want, but since our app expects camelCase keys, what I really need is this
[{"id": 1, "someCol": "Hello there", "anotherVal": "2020-01-01"}, {"id": 2, "someCol": "Bonjour", "anotherVal": "1984-02-23"}]
Ideally, I want a way to write this for virtually any select statement
select jsonb_agg_with_camel_keys(f)
from test.foo f;
-- or for functions
select jsonb_agg_with_camel_keys(f)
from test.some_function(param_one, param_two) f;
However, such a function does not exist that I am aware of.
For now, we are doing this
select array_to_json(array_agg(row_to_json(foos)))
from
(
select
f.id as id,
f.some_col as "someCol",
f.another_val as "anotherVal"
from
test.foo f
) foos;
This works, but since we use this pattern regularly, it is tedious, error prone (typos), and takes more work to maintain than a generic solution would (especially when there are multiple nested levels of select statements).
Ideally, the solution would automatically turn snake_case to camelCase for all keys (included multiple levels of nested json). The solution would be dynamic (accepting any input like the jsonb_agg function already does). The solution could be used for table and function results. Note that only json keys need to be altered. All other output should remain unchanged.
In theory, I could write some sort of regex_replace that replaces json keys after the aggregation is done. While certainly a possibility, it seems that it would be more efficient to do the conversion during the aggregation process. I understand that postgresql expects well defined types, which makes writing a plpgsql function difficult. I have tried thinking of a way to do this, but doing it for function results would be difficult. I also understand that one option is writing my own aggregate function in C, basically a variant of jsonb_agg (if this is the best option, then a general description of how to do so would be appreciated, since I have not done that before).
Thank you in advance for your time and thoughts.
EDIT
Renaming column names is not a good solution for me. It would require a major refactor and would be much more work and trouble than it's worth.