Postgres Pro
Downloads
Home   >   mailing lists

[GENERAL] How to stop array_to_json from interpolating column names thatweren't there - Mailing list pgsql-general

From Guyren Howe
Subject [GENERAL] How to stop array_to_json from interpolating column names thatweren't there
Date
Msg-id 9F77A35D-2FB2-4CB7-90BB-9DBBA8B289C4@gmail.com
Whole thread Raw
Responses Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: [GENERAL] How to stop array_to_json from interpolating columnnames that weren't there  (Paul Jungwirth <pj@illuminatedcomputing.com>)
List pgsql-general
Tree view
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?

pgsql-general by date:

Previous
From: Alessandro_feliz
Date:
Subject: [GENERAL] Postgres csv logging
Next
From: Glen Huang
Date:
Subject: Re: [GENERAL] Is it possible to define a constraint based on thevalues in other rows in the current table?