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?