Обсуждение: [GENERAL] How to stop array_to_json from interpolating column names thatweren't there
9.6
I’ve a view that shows information about schemas:
SELECT schemata.catalog_name,
schemata.schema_name,
('/'::text || (schemata.schema_name)::text) AS schema_name_address
FROM information_schema.schemata
ORDER BY schemata.catalog_name, schemata.schema_name
Fine. I now want to turn that result set into a JSON array. I can do this:
SELECT
ARRAY_AGG(foo) AS foos
FROM (
SELECT
row_to_json(schemata_)
FROM
schemata_)
AS
foo
and I get this:
{“(\”{\"\"catalog_name\"\":\"\"ds2_development\"\",\"\"schema_name\"\":\"\"admin\"\",\"\"schema_name_address\"\":\"\"/admin\"\"}\")","(\"…
which is great. I have an array of perfect JSON objects. Now I just need to turn that into a single JSON object. But when I do the obvious:
SELECT array_to_json(SELECTARRAY_AGG(foo) AS foosFROM (SELECTrow_to_json(schemata_)FROMschemata_)ASfoo)
I get a syntax error. And when I do:
SELECT TO_JSON(foos) FROM (
SELECT
ARRAY_AGG(foo) AS foos
FROM (
SELECT
row_to_json(schemata_)
FROM
schemata_)
AS
foo
) AS bar
Postgres tries to be helpful by interpolating a column name I don’t want (here, row_to_json):
[{“row_to_json":{"catalog_name":"ds2_development","schema_name":"admin","schema_name_address":"/admin"}},{"row_to_json":{"catalog_name":"ds2_development","schema_name":"anon","schema_name_address":"/anon"}},{"row_to_json":
…
I could fix this in PLV8 a tad inefficiently, but I feel like I ought to be able to do this in pure SQL and there’s some heuristic or Yet Another Weird Corner Case I’m as yet unaware of.
So: can I do this in SQL? How?
On Jul 19, 2017, at 20:47 , Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
which is great. I have an array of perfect JSON objects. Now I just need
to turn that into a single JSON object.
I think you're saying you want it as a single JSON *array*, right? An object of objects doesn't make sense. Assuming that's right, this seems to work:
db1=# select json_agg(schemata) from schemata;
json_agg
----------------------------------------------------------------------------------------------------------
[{“catalog_name":"db1","schema_name":"information_schema","schema_name_address":"/information_schema"},
Thanks. Seeking greater understanding, why is json_agg(*) not equivalent?
Thanks. Seeking greater understanding, why is json_agg(*) not equivalent?
Are you referring to the fact that this provokes an error?
"select json_agg(*) from schemata;"
The json_agg(expression) function has an arity of 1 (i.e., one input argument only). If you write:
select schemata from schemata;
you get one column in the output, while:
select * from schemata
results in an output relation having three columns - the "*" expands the composite type in the FROM clause into its component columns in the select-list
json_agg(*) fails since it is not expecting 3 columns (though oddly the error I see in 9.6 indicates its actually looking for a 0-arity function... "function json_agg() does not exist" - which implies that generally one cannot place a * in a function call, even if it would result in a single column. This may even be documented but I haven't the desire to look right now...)
David J.