Trying to json_agg multiple columns throws an error "No function matches the given name..."

user_15267438 0
Viewed 1942

Im trying to use json_agg function on postgresql but it keeps throwing error saying

ERROR:  function json_agg(character varying, character varying) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883"

The problem comes when i am trying to agg multiple columns but not all.

i.e

SELECT json_agg(users.*) as users 
FROM users

works fine, same with single column query

i.e

SELECT json_agg(users.id) as users FROM users

but if I'm trying to agg multiple columns i.e

SELECT json_agg(users.id, users.name, users.address) as users 
FROM users

it throws error shown above, why? Is that not possible or is there some specific syntax required?

postgresql
1 Answers

json_agg() only takes a single parameter, not multiple parameters.

If you want to convert the whole row into a JSON and then aggregate that, pass the table reference:

SELECT json_agg(users) as users 
FROM users

If you only want some columns, use json_build_object()

SELECT json_agg(json_build_object('id', users.id, 'name', users.name)) as users 
FROM users

If you want most of the columns and just exclude a few, using to_jsonb() and removing the unwanted ones might be easier:

SELECT jsonb_agg((to_jsonb(users) - 'password')) as users 
FROM users
user_3303150
Related