From: Joe Conway <mail@joeconway.com>
To: Sehrope Sarkuni <sehrope@jackdb.com>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Davin Shearer <davin@apache.org>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Emitting JSON to file using COPY TO
Date: Wed, 6 Dec 2023 16:28:59 -0500
Message-ID: <4d5688f4-9582-4093-8448-e1867bc9e2bc@joeconway.com> (raw)
In-Reply-To: <CAH7T-aoWF9oHuUC2PJ303YVRFurkLMPayHawZayqdhtT2fpEHg@mail.gmail.com>
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>
<5c84b70b-ba18-c45d-dbbe-612fa229b2ce@dunslane.net>
<CALvfUkDxgTppjy-Q_i2znakMFp2vrJdPUEJNyGTL2LcZBOWV6A@mail.gmail.com>
<cbd65026-d79c-40b1-8be3-10e1db2fc092@joeconway.com>
<398c22f6-4299-4b17-80bf-2f14f4afd592@joeconway.com>
<CALvfUkAbj4Bwg0FjYpvj0-zddcetWg1Sejt=OGBTDskbqOFS_A@mail.gmail.com>
<46cc4507-a0d9-4044-b2ce-5a8bca8015c0@joeconway.com>
<CALvfUkD8+bcNs1cYeU3wK9YsyZ7zUjs2aTxO+++Vq7zKeUE7+w@mail.gmail.com>
<2554e520-e103-8978-dcb5-807dfeb77402@dunslane.net>
<cb1f118c-4698-4451-9ba9-dd8595f06c37@joeconway.com>
<926ff917-8371-40ec-b5e6-ab7b0e09bdc5@joeconway.com>
<315b81d4-4b67-7828-0355-3808cd14acd1@dunslane.net>
<7a60faf6-e7f1-419d-aee6-10a78ea2fe81@joeconway.com>
<2e7ff718-895d-83fc-46f7-be25e23b23b4@dunslane.net>
<1104915.1701877459@sss.pgh.pa.us>
<19a5f9d8-bd1f-9e51-0f5b-510c1189a8a7@dunslane.net>
<CAH7T-aoWF9oHuUC2PJ303YVRFurkLMPayHawZayqdhtT2fpEHg@mail.gmail.com>
On 12/6/23 11:28, Sehrope Sarkuni wrote:
> Big +1 to this overall feature.
cool!
> Regarding the defaults for the output, I think JSON lines (rather than a
> JSON array of objects) would be preferred. It's more natural to combine
> them and generate that type of data on the fly rather than forcing
> aggregation into a single object.
So that is +2 (Sehrope and me) for the status quo (JSON lines), and +2
(Andrew and Davin) for defaulting to json arrays. Anyone else want to
weigh in on that issue?
> Couple more features / use cases come to mind as well. Even if they're
> not part of a first round of this feature I think it'd be helpful to
> document them now as it might give some ideas for what does make that
> first cut:
>
> 1. Outputting a top level JSON object without the additional column
> keys. IIUC, the top level keys are always the column names. A common use
> case would be a single json/jsonb column that is already formatted
> exactly as the user would like for output. Rather than enveloping it in
> an object with a dedicated key, it would be nice to be able to output it
> directly. This would allow non-object results to be outputted as well
> (e.g., lines of JSON arrays, numbers, or strings). Due to how JSON is
> structured, I think this would play nice with the JSON lines v.s. array
> concept.
>
> COPY (SELECT json_build_object('foo', x) AS i_am_ignored FROM
> generate_series(1, 3) x) TO STDOUT WITH (FORMAT JSON,
> SOME_OPTION_TO_NOT_ENVELOPE)
> {"foo":1}
> {"foo":2}
> {"foo":3}
Your example does not match what you describe, or do I misunderstand? I
thought your goal was to eliminate the repeated "foo" from each row...
> 2. An option to ignore null fields so they are excluded from the output.
> This would not be a default but would allow shrinking the total size of
> the output data in many situations. This would be recursive to allow
> nested objects to be shrunk down (not just the top level). This might be
> worthwhile as a standalone JSON function though handling it during
> output would be more efficient as it'd only be read once.
>
> COPY (SELECT json_build_object('foo', CASE WHEN x > 1 THEN x END) FROM
> generate_series(1, 3) x) TO STDOUT WITH (FORMAT JSON,
> SOME_OPTION_TO_NOT_ENVELOPE, JSON_SKIP_NULLS)
> {}
> {"foo":2}
> {"foo":3}
clear enough I think
> 3. Reverse of #2 when copying data in to allow defaulting missing fields
> to NULL.
good to record the ask, but applies to a different feature (COPY FROM
instead of COPY TO).
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: mail@joeconway.com, sehrope@jackdb.com, andrew@dunslane.net, tgl@sss.pgh.pa.us, davin@apache.org, pgsql-hackers@postgresql.org
Subject: Re: Emitting JSON to file using COPY TO
In-Reply-To: <4d5688f4-9582-4093-8448-e1867bc9e2bc@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox