Message-ID: <4ad29199-361f-4740-a919-12080ad5ed65@joeconway.com>
Date: Wed, 6 Dec 2023 17:38:21 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Emitting JSON to file using COPY TO
Content-Language: en-US
To: Sehrope Sarkuni <sehrope@jackdb.com>
Cc: Andrew Dunstan <andrew@dunslane.net>, Tom Lane <tgl@sss.pgh.pa.us>,
 Davin Shearer <davin@apache.org>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@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>
 <4d5688f4-9582-4093-8448-e1867bc9e2bc@joeconway.com>
 <CAH7T-aow6jM-v1K462O7N=doOZxSD79zsH6prDCft7wv91F48A@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Autocrypt: addr=mail@joeconway.com; keydata=
 xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l
 VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS
 sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14
 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE
 qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw
 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ
 ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo
 DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA
 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm
 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzSRKb3NlcGggRSBD
 b253YXkgPG1haWxAam9lY29ud2F5LmNvbT7CwXoEEwEIACQCGwMCHgECF4AFCwkIBwMFFQoJ
 CAsFFgIDAQAFAlWTVvUCGQEACgkQMyt+aLaZQ0oPCQ/9HyRewMyvAIJRmoXoLAr8AoFLId6R
 qBJnNX0Lll0RLZui65aQ0+exwX7aH7TxWR16B2gWX3OmLfGT8XITOoG+zt9zsEpLvNkHchkF
 T/jyAcbuRj5WX9hamZgMbjXAJeCdlhW+fRA9Upb0w4dgBjqK5OgsqMikASL7t2vogHl9H08j
 vSoQLW+8wTnSBXBeBTBwB7xLIin5WVivzFHUCrnD2UsjeBIW3fmGdpTAjSxRzG+UPYVwXQ8F
 FLt7DpEytvLWapmZWMRdj0WZ/Q3SOO/Ed0yFqbzuwKaWcFrQBNeS2Sig+FefBNS98f9Hx7ku
 H3DW34qX/zSSdDh0jLs7X3PkIgF6BZR2TxaCwHPP9ERDiDaUInC9U7We1iZE1DjW8rLMEVJB
 hY0ClrrF67pnUKTbcU+uajpPn+2Jl74T0Set/XxpHZ4cezcJuqg31R8vHZgd5cf1WKP0D0pc
 qiuS02BBFkNCs1jQ+raTWcDuE6F1mUO2nvjUBN9r4y5DUbCNSqLKeAe/aA6JaSDkBpoXKdNS
 +c4rbzbktWkfUW8EhVlCGzNpy4ezEoVsqV2Ex7fNoxsE2vnSylLT9hycAmYf8ryMvniRZqnD
 T4JgLenIcQlkhB896T7wApOXfD8OJj1/XFxAfPi6vdlsr81uoxuB4euLp8IyduwLORRUogO9
 zmAXG5jOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMva
 v/KENd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFV
 dWnlw7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5Vu
 gWNlPZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv
 5ZhPRpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc
 2OrWo7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQ
 bauDQw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT
 0S5yOgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4e
 NrxU0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA
 81uMj49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEI
 AAkFAkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4
 Fia379JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBG
 HwLEOTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZ
 E0C1xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6Xlnd
 FrDlY22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl
 0ZJP3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcH
 YebVJZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs
 5egE3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1
 c9JbPJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+
 1iJNEnp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q=
In-Reply-To: <CAH7T-aow6jM-v1K462O7N=doOZxSD79zsH6prDCft7wv91F48A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/4ad29199-361f-4740-a919-12080ad5ed65%40joeconway.com>
Precedence: bulk

On 12/6/23 16:42, Sehrope Sarkuni wrote:
> On Wed, Dec 6, 2023 at 4:29 PM Joe Conway <mail@joeconway.com 
> <mailto:mail@joeconway.com>> wrote:
> 
>      > 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...
> 
> 
> The "foo" in this case is explicit as I'm adding it when building the 
> object. What I was trying to show was not adding an additional object 
> wrapper / envelope.
> 
> So each row is:
> 
> {"foo":1}
> 
> Rather than:
> 
> "{"json_build_object":{"foo":1}}

I am still getting confused ;-)

Let's focus on the current proposed patch with a "minimum required 
feature set".

Right now the default behavior is "JSON lines":
8<-------------------------------
COPY (SELECT x.i, 'val' || x.i as v FROM
       generate_series(1, 3) x(i))
TO STDOUT WITH (FORMAT JSON);
{"i":1,"v":"val1"}
{"i":2,"v":"val2"}
{"i":3,"v":"val3"}
8<-------------------------------

and the other, non-default option is "JSON array":
8<-------------------------------
COPY (SELECT x.i, 'val' || x.i as v FROM
       generate_series(1, 3) x(i))
TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);
[
  {"i":1,"v":"val1"}
,{"i":2,"v":"val2"}
,{"i":3,"v":"val3"}
]
8<-------------------------------

So the questions are:
1. Do those two formats work for the initial implementation?
2. Is the default correct or should it be switched
    e.g. rather than specifying FORCE_ARRAY to get an
    array, something like FORCE_NO_ARRAY to get JSON lines
    and the JSON array is default?

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com