Horton

Re: Emitting JSON to file using COPY TO

Dominique Devienne <ddevienne@gmail.com>

View thread
From: Dominique Devienne <ddevienne@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Davin Shearer <scholarsmate@gmail.com>, pgsql-general@lists.postgresql.org
Date: 2023-11-27T09:44:55Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add option force_array for COPY JSON FORMAT

  2. json format for COPY TO

  3. introduce CopyFormat, refactor CopyFormatOptions

  4. Doc: add IDs to copy.sgml's <varlistentry> and <refsect1>

On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

> On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> On 11/25/23 11:21, Davin Shearer wrote:
>> > Hello!
>> >
>> > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY
>> > TO, but I'm running into problems with COPY TO double quoting the
>> > output.   Here is a minimal example that demonstrates the problem I'm
>> > having:
>> >
>>
>> > I have tried to get COPY TO to copy the results to file "as-is" by
>> > setting the escape and the quote characters to the empty string (''),
>> > but they only apply to the CSV format.
>> >
>> > Is there a way to emit JSON results to file from within postgres?
>> > Effectively, nn "as-is" option to COPY TO would work well for this JSON
>> > use case.
>> >
>>
>> Not using COPY.
>>
>> See David Johnson's post for one way using the client psql.
>>
>> Otherwise you will need to use any of the many ETL programs out there
>> that are designed for this sort of thing.
>>
>
> Guys, I don't get answers like that. The JSON spec is clear:
>

Oops, sorry, user error. --DD

PS: The JSON spec is a bit ambiguous. First it says

> Any codepoint except " or \ or control characters

And then is clearly shows \" as a valid sequence...
Sounds like JQ is too restrictive?

Or that's the double-escape that's the culprit?
i.e. \\ is in the final text, so that's just a backslash,
and then the double-quote is no longer escaped.

I've recently noticed json_agg(row_to_json(t))
is equivalent to json_agg(t)

Maybe use that instead? Does that make a difference?

I haven't noticed wrong escaping of double-quotes yet,
but then I'm using the binary mode of queries. Perhaps that matters.

On second thought, I guess that's COPY in its text modes doing the escaping?
Interesting. The text-based modes of COPY are configurable. There's even a
JSON mode.
By miracle, would the JSON output mode recognize JSON[B] values, and avoid
the escaping?