v25-0003-Add-option-force_array-for-COPY-JSON-FORMAT.patch

From 312dedc0a3aea1833bb992f096121db4086fd075 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 9 Feb 2026 11:14:15 +0800
Subject: [PATCH v25 3/4] Add option force_array for COPY JSON FORMAT

---
 doc/src/sgml/ref/copy.sgml         | 30 +++++++++++++++++++++
 src/backend/commands/copy.c        | 13 +++++++++
 src/backend/commands/copyto.c      | 43 +++++++++++++++++++++++++++---
 src/bin/psql/tab-complete.in.c     |  2 +-
 src/include/commands/copy.h        |  1 +
 src/test/regress/expected/copy.out | 23 ++++++++++++++++
 src/test/regress/sql/copy.sql      |  8 ++++++
 7 files changed, 115 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 75f55bbf6f8..a79587f7613 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -40,6 +40,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     HEADER [ <replaceable class="parameter">boolean</replaceable> | <replaceable class="parameter">integer</replaceable> | MATCH ]
     QUOTE '<replaceable class="parameter">quote_character</replaceable>'
     ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
+    FORCE_ARRAY [ <replaceable class="parameter">boolean</replaceable> ]
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
@@ -366,6 +367,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry id="sql-copy-params-force-array">
+    <term><literal>FORCE_ARRAY</literal></term>
+    <listitem>
+     <para>
+      Force output of square brackets as array decorations at the beginning
+      and end of output, and commas between the rows. It is allowed only in
+      <command>COPY TO</command>, and only when using
+      <literal>json</literal> format. The default is
+      <literal>false</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="sql-copy-params-force-quote">
     <term><literal>FORCE_QUOTE</literal></term>
     <listitem>
@@ -1103,6 +1117,22 @@ COPY country TO STDOUT (DELIMITER '|');
 </programlisting>
   </para>
 
+<para>
+   When the <literal>FORCE_ARRAY</literal> option is enabled,
+   the entire output is wrapped in a single JSON array with rows separated by commas:
+<programlisting>
+COPY (SELECT * FROM (VALUES(1),(2)) val(id)) TO STDOUT  (FORMAT JSON, FORCE_ARRAY);
+</programlisting>
+The output is as follows:
+<screen>
+[
+ {"id":1}
+,{"id":2}
+]
+</screen>
+</para>
+
+
   <para>
    To copy data from a file into the <literal>country</literal> table:
 <programlisting>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 29c121c7f08..84254d46a67 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -569,6 +569,7 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		on_error_specified = false;
 	bool		log_verbosity_specified = false;
 	bool		reject_limit_specified = false;
+	bool		force_array_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -725,6 +726,13 @@ ProcessCopyOptions(ParseState *pstate,
 								defel->defname),
 						 parser_errposition(pstate, defel->location)));
 		}
+		else if (strcmp(defel->defname, "force_array") == 0)
+		{
+			if (force_array_specified)
+				errorConflictingDefElem(defel, pstate);
+			force_array_specified = true;
+			opts_out->force_array = defGetBoolean(defel);
+		}
 		else if (strcmp(defel->defname, "on_error") == 0)
 		{
 			if (on_error_specified)
@@ -967,6 +975,11 @@ ProcessCopyOptions(ParseState *pstate,
 				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				errmsg("COPY %s mode cannot be used with %s", "JSON", "COPY FROM"));
 
+	if (opts_out->format != COPY_FORMAT_JSON && opts_out->force_array)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s can only be used with JSON mode", "FORCE_ARRAY"));
+
 	if (opts_out->default_print)
 	{
 		if (!is_from)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 96605079eeb..a7615cc34ec 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -88,6 +88,7 @@ typedef struct CopyToStateData
 	bool		is_program;		/* is 'filename' a program to popen? */
 
 	/* JSON format state */
+	bool		json_row_delim_needed;	/* need delimiter before next row */
 	bool		json_tupledesc_ready;	/* TupleDesc setup done for JSON */
 	StringInfoData json_buf;	/* reusable buffer for JSON output */
 
@@ -138,6 +139,7 @@ static void CopyToTextLikeOneRow(CopyToState cstate, TupleTableSlot *slot,
 								 bool is_csv);
 static void CopyToTextLikeEnd(CopyToState cstate);
 static void CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot);
+static void CopyToJsonEnd(CopyToState cstate);
 static void CopyToBinaryStart(CopyToState cstate, TupleDesc tupDesc);
 static void CopyToBinaryOutFunc(CopyToState cstate, Oid atttypid, FmgrInfo *finfo);
 static void CopyToBinaryOneRow(CopyToState cstate, TupleTableSlot *slot);
@@ -157,8 +159,9 @@ static void CopySendInt16(CopyToState cstate, int16 val);
 /*
  * COPY TO routines for built-in formats.
  *
- * CSV, text and json formats share the same TextLike routines except for the
- * one-row callback.
+ * Text and CSV formats share the same TextLike routines except for the
+ * one-row callback.  JSON shares the start and outfunc callbacks with
+ * text/CSV, but has its own one-row and end callbacks.
  */
 
 /* text format */
@@ -182,7 +185,7 @@ static const CopyToRoutine CopyToRoutineJson = {
 	.CopyToStart = CopyToTextLikeStart,
 	.CopyToOutFunc = CopyToTextLikeOutFunc,
 	.CopyToOneRow = CopyToJsonOneRow,
-	.CopyToEnd = CopyToTextLikeEnd,
+	.CopyToEnd = CopyToJsonEnd,
 };
 
 /* binary format */
@@ -258,6 +261,15 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 		oldcxt = MemoryContextSwitchTo(cstate->copycontext);
 		initStringInfo(&cstate->json_buf);
 		MemoryContextSwitchTo(oldcxt);
+
+		/*
+		 * If FORCE_ARRAY has been specified, send the opening bracket.
+		 */
+		if (cstate->opts.force_array)
+		{
+			CopySendChar(cstate, '[');
+			CopySendTextLikeEndOfRow(cstate);
+		}
 	}
 }
 
@@ -335,7 +347,7 @@ CopyToTextLikeOneRow(CopyToState cstate,
 	CopySendTextLikeEndOfRow(cstate);
 }
 
-/* Implementation of the end callback for text, CSV, and json formats */
+/* Implementation of the end callback for text and CSV formats */
 static void
 CopyToTextLikeEnd(CopyToState cstate)
 {
@@ -371,11 +383,34 @@ CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
 	resetStringInfo(&cstate->json_buf);
 	composite_to_json(rowdata, &cstate->json_buf, false);
 
+	if (cstate->opts.force_array)
+	{
+		if (cstate->json_row_delim_needed)
+			CopySendChar(cstate, ',');
+		else
+		{
+			/* first row needs no delimiter */
+			CopySendChar(cstate, ' ');
+			cstate->json_row_delim_needed = true;
+		}
+	}
+
 	CopySendData(cstate, cstate->json_buf.data, cstate->json_buf.len);
 
 	CopySendTextLikeEndOfRow(cstate);
 }
 
+/* Implementation of the end callback for json format */
+static void
+CopyToJsonEnd(CopyToState cstate)
+{
+	if (cstate->opts.force_array)
+	{
+		CopySendChar(cstate, ']');
+		CopySendTextLikeEndOfRow(cstate);
+	}
+}
+
 /*
  * Implementation of the start callback for binary format. Send a header
  * for a binary copy.
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index d257837b0c5..76d3258e92b 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1232,7 +1232,7 @@ Copy_common_options, "DEFAULT", "FORCE_NOT_NULL", "FORCE_NULL", "FREEZE", \
 
 /* COPY TO options */
 #define Copy_to_options \
-Copy_common_options, "FORCE_QUOTE"
+Copy_common_options, "FORCE_QUOTE", "FORCE_ARRAY"
 
 /*
  * These object types were introduced later than our support cutoff of
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 2b5bef6738e..abecfe51098 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -88,6 +88,7 @@ typedef struct CopyFormatOptions
 	List	   *force_notnull;	/* list of column names */
 	bool		force_notnull_all;	/* FORCE_NOT_NULL *? */
 	bool	   *force_notnull_flags;	/* per-column CSV FNN flags */
+	bool		force_array;	/* add JSON array decorations */
 	List	   *force_null;		/* list of column names */
 	bool		force_null_all; /* FORCE_NULL *? */
 	bool	   *force_null_flags;	/* per-column CSV FN flags */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 4ea658a45de..a7e88b711d7 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -114,6 +114,29 @@ ERROR:  COPY JSON mode cannot be used with COPY FROM
 copy copytest (style) to stdout (format json);
 ERROR:  column selection is not supported in JSON mode
 -- all of the above should yield error
+-- should fail: force_array requires json format
+copy copytest to stdout (format csv, force_array true);
+ERROR:  COPY FORCE_ARRAY can only be used with JSON mode
+-- force_array variants
+copy copytest to stdout (format json, force_array);
+[
+ {"style":"DOS","test":"abc\r\ndef","filler":1}
+,{"style":"Unix","test":"abc\ndef","filler":2}
+,{"style":"Mac","test":"abc\rdef","filler":3}
+,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+]
+copy copytest to stdout (format json, force_array true);
+[
+ {"style":"DOS","test":"abc\r\ndef","filler":1}
+,{"style":"Unix","test":"abc\ndef","filler":2}
+,{"style":"Mac","test":"abc\rdef","filler":3}
+,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+]
+copy copytest to stdout (format json, force_array false);
+{"style":"DOS","test":"abc\r\ndef","filler":1}
+{"style":"Unix","test":"abc\ndef","filler":2}
+{"style":"Mac","test":"abc\rdef","filler":3}
+{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
 -- embedded escaped characters
 create temp table copyjsontest (
     id bigserial,
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index c558eba202a..ae202fc5e8d 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -102,6 +102,14 @@ copy copytest from stdin(format json);
 copy copytest (style) to stdout (format json);
 -- all of the above should yield error
 
+-- should fail: force_array requires json format
+copy copytest to stdout (format csv, force_array true);
+
+-- force_array variants
+copy copytest to stdout (format json, force_array);
+copy copytest to stdout (format json, force_array true);
+copy copytest to stdout (format json, force_array false);
+
 -- embedded escaped characters
 create temp table copyjsontest (
     id bigserial,
-- 
2.43.0