Обсуждение: BUG #9168: Invalid JSON output generated by SQL statement

The following bug has been logged on the website:

Bug reference:      9168
Logged by:          Gary Teh
Email address:      garyjob@krake.io
PostgreSQL version: 9.2.2
Operating system:   MacOS X 10.7.5
Description:

Statement to create the database table
===
CREATE TABLE test_tables (
    properties hstore,
    "pingedAt" timestamp with time zone,
    id integer NOT NULL,
    "createdAt" timestamp with time zone NOT NULL,
    "updatedAt" timestamp with time zone NOT NULL
);

Statement to create a record
===
INSERT INTO
"test_tables"
("createdAt","updatedAt","pingedAt","properties") VALUES
('2014-2-10 0:10:28','2014-2-10 0:10:28','2014-2-10 0:10:28','"drug bank" =>
"cache \"that stuff"')

Statement to export the record into a JSON file
===
Copy (
  select
    array_to_json(
      array_agg( row_to_json(row)
    )
  )
  from (
    SELECT
      properties::hstore->'drug bank' as "drug bank",
      "pingedAt"
    FROM
      "test_tables"
    WHERE true
  ) row
) To '/tmp/invalid_json_output'

The invalid JSON output
[{
  "drug bank":"cache \\"that stuff",
  "pingedAt":"2014-02-09 16:10:28-08"
},{
  "drug bank":"cache \\"that stuff",
  "pingedAt":"2014-02-10 00:10:28-08"
}]

The final JSON output is not valid.

garyjob@krake.io writes:
> The invalid JSON output
> [{
>   "drug bank":"cache \\"that stuff",
>   "pingedAt":"2014-02-09 16:10:28-08"
> },{
>   "drug bank":"cache \\"that stuff",
>   "pingedAt":"2014-02-10 00:10:28-08"
> }]

> The final JSON output is not valid.

Hmm, I can't claim to have memorized the JSON spec, but that looks
generally sane to me.  What do you think is invalid about it, exactly?

(If you're complaining about the backslash having been doubled, that's
an effect of the COPY, not a bug in the JSON output function.)

            regards, tom lane