From: | Guyren Howe <guyren(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | How to stop array_to_json from interpolating column names that weren't there |
Date: | 2017-07-20 02:30:18 |
Message-ID: | 9F77A35D-2FB2-4CB7-90BB-9DBBA8B289C4@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(
SELECT
ARRAY_AGG(foo) AS foos
FROM (
SELECT
row_to_json(schemata_)
FROM
schemata_)
AS
foo
)
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Glen Huang | 2017-07-20 02:55:50 | Re: Is it possible to define a constraint based on the values in other rows in the current table? |
Previous Message | Alessandro_feliz | 2017-07-19 23:41:56 | Postgres csv logging |