0

With postgresql version 9.2, I'm trying to run a query that selects certain columns to build a json object. Here's the query, which I derived from this question:

select
  a.id
  , json_agg((SELECT x FROM (SELECT b.fieldA) AS x)) AS item
from a join b on a.id = b.foreign_key
limit 10

When I run this, I get an error that's rather hard to parse.

ERROR: function json_agg(record) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

, json_agg((SELECT x FROM (SELECT b.fieldA) AS x)) AS item

It seems as though the complaint is that json_agg is not receiving the right sort of input, but I don't understand why.

When I run the same query without the json_agg, i.e. just with the sub-select expression, I get a column with the value of b.fieldA inside parentheses. I'm not sure what the parens indicate in terms of a data type, but I could see that perhaps json_agg is only getting the values an so doesn't know how to supply the property name.

Can anyone explain where I've gone wrong?

1

json_agg does not exist in postgres version 9.2 (which is now at end-of-life). It was introduced in version 9.3.

The HINT does seem to be slightly inappropriate. If no function by that name exists, then no amount of casting the arguments can work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.