v27-0002-json-format-for-COPY-TO.patch

From b3222f265bd157690d5f69ea8eba84be13e5910a Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 6 Mar 2026 14:32:48 +0800
Subject: [PATCH v27 2/4] json format for COPY TO

This introduces the JSON format option for the COPY TO command, allowing users
to export query results or table data directly as a single JSON object or a
stream of JSON objects.

The JSON format is currently supported only for COPY TO operations; it
is not available for COPY FROM.

JSON format is incompatible with some standard text/CSV parsing or formatting options,
including:
- HEADER
- DEFAULT
- NULL
- DELIMITER
- FORCE QUOTE / FORCE NOT NULL

Regression tests covering valid JSON exports and error handling for
incompatible options have been added to src/test/regress/sql/copy.sql.

Author: Joe Conway <mail@joeconway.com>
Author: jian he <jian.universality@gmail.com>
Reviewed-by: "Andrey M. Borodin" <x4mmm@yandex-team.ru>,
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>,
Reviewed-by: Daniel Verite <daniel@manitou-mail.org>,
Reviewed-by: Andrew Dunstan <andrew@dunslane.net>,
Reviewed-by: Davin Shearer <davin@apache.org>,
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>,
Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Junwang Zhao <zhjwpku@gmail.com>

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/6a04628d-0d53-41d9-9e35-5a8dc302c34c@joeconway.com
---
 doc/src/sgml/ref/copy.sgml         | 13 +++--
 src/backend/commands/copy.c        | 49 ++++++++++++-----
 src/backend/commands/copyto.c      | 85 +++++++++++++++++++++++++----
 src/backend/parser/gram.y          |  8 +++
 src/backend/utils/adt/json.c       |  5 +-
 src/bin/psql/tab-complete.in.c     |  2 +-
 src/include/commands/copy.h        |  1 +
 src/include/utils/json.h           |  2 +
 src/test/regress/expected/copy.out | 86 ++++++++++++++++++++++++++++++
 src/test/regress/sql/copy.sql      | 51 ++++++++++++++++++
 10 files changed, 271 insertions(+), 31 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 0ad890ef95f..75f55bbf6f8 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -228,10 +228,15 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       Selects the data format to be read or written:
       <literal>text</literal>,
       <literal>csv</literal> (Comma Separated Values),
+      <literal>json</literal> (JavaScript Object Notation),
       or <literal>binary</literal>.
       The default is <literal>text</literal>.
       See <xref linkend="sql-copy-file-formats"/> below for details.
      </para>
+     <para>
+      The <literal>json</literal> option is allowed only in
+      <command>COPY TO</command>.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -266,7 +271,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       (line) of the file.  The default is a tab character in text format,
       a comma in <literal>CSV</literal> format.
       This must be a single one-byte character.
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -280,7 +285,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       string in <literal>CSV</literal> format. You might prefer an
       empty string even in text format for cases where you don't want to
       distinguish nulls from empty strings.
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
 
      <note>
@@ -303,7 +308,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       is found in the input file, the default value of the corresponding column
       will be used.
       This option is allowed only in <command>COPY FROM</command>, and only when
-      not using <literal>binary</literal> format.
+      not using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -330,7 +335,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       <command>COPY FROM</command> commands.
      </para>
      <para>
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 2f46be516f2..29c121c7f08 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -597,6 +597,8 @@ ProcessCopyOptions(ParseState *pstate,
 				opts_out->format = COPY_FORMAT_CSV;
 			else if (strcmp(fmt, "binary") == 0)
 				opts_out->format = COPY_FORMAT_BINARY;
+			else if (strcmp(fmt, "json") == 0)
+				opts_out->format = COPY_FORMAT_JSON;
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -756,21 +758,32 @@ ProcessCopyOptions(ParseState *pstate,
 	 * Check for incompatible options (must do these three before inserting
 	 * defaults)
 	 */
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->delim)
+	if (opts_out->delim &&
+		(opts_out->format == COPY_FORMAT_BINARY ||
+		 opts_out->format == COPY_FORMAT_JSON))
 		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
-				 errmsg("cannot specify %s in BINARY mode", "DELIMITER")));
+				errcode(ERRCODE_SYNTAX_ERROR),
+				opts_out->format == COPY_FORMAT_BINARY
+				? errmsg("cannot specify %s in BINARY mode", "DELIMITER")
+				: errmsg("cannot specify %s in JSON mode", "DELIMITER"));
 
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->null_print)
+	if (opts_out->null_print &&
+		(opts_out->format == COPY_FORMAT_BINARY ||
+		 opts_out->format == COPY_FORMAT_JSON))
 		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("cannot specify %s in BINARY mode", "NULL")));
+				errcode(ERRCODE_SYNTAX_ERROR),
+				opts_out->format == COPY_FORMAT_BINARY
+				? errmsg("cannot specify %s in BINARY mode", "NULL")
+				: errmsg("cannot specify %s in JSON mode", "NULL"));
 
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->default_print)
+	if (opts_out->default_print &&
+		(opts_out->format == COPY_FORMAT_BINARY ||
+		 opts_out->format == COPY_FORMAT_JSON))
 		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("cannot specify %s in BINARY mode", "DEFAULT")));
+				errcode(ERRCODE_SYNTAX_ERROR),
+				opts_out->format == COPY_FORMAT_BINARY
+				? errmsg("cannot specify %s in BINARY mode", "DEFAULT")
+				: errmsg("cannot specify %s in JSON mode", "DEFAULT"));
 
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
@@ -836,11 +849,15 @@ ProcessCopyOptions(ParseState *pstate,
 				 errmsg("COPY delimiter cannot be \"%s\"", opts_out->delim)));
 
 	/* Check header */
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->header_line != COPY_HEADER_FALSE)
+	if (opts_out->header_line != COPY_HEADER_FALSE &&
+		(opts_out->format == COPY_FORMAT_BINARY ||
+		 opts_out->format == COPY_FORMAT_JSON))
 		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
-				 errmsg("cannot specify %s in BINARY mode", "HEADER")));
+				opts_out->format == COPY_FORMAT_BINARY
+				? errmsg("cannot specify %s in BINARY mode", "HEADER")
+				: errmsg("cannot specify %s in JSON mode", "HEADER"));
 
 	/* Check quote */
 	if (opts_out->format != COPY_FORMAT_CSV && opts_out->quote != NULL)
@@ -944,6 +961,12 @@ ProcessCopyOptions(ParseState *pstate,
 				 errmsg("COPY %s cannot be used with %s", "FREEZE",
 						"COPY TO")));
 
+	/* Check json format */
+	if (opts_out->format == COPY_FORMAT_JSON && is_from)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s mode cannot be used with %s", "JSON", "COPY FROM"));
+
 	if (opts_out->default_print)
 	{
 		if (!is_from)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 0325a16f82a..e87310ec5a0 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -26,6 +26,7 @@
 #include "executor/execdesc.h"
 #include "executor/executor.h"
 #include "executor/tuptable.h"
+#include "funcapi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
@@ -33,6 +34,7 @@
 #include "pgstat.h"
 #include "storage/fd.h"
 #include "tcop/tcopprot.h"
+#include "utils/json.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
@@ -84,6 +86,8 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	StringInfo	json_buf;		/* reusable buffer for JSON output, it is
+								 * initliazed in BeginCopyTo  */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -130,6 +134,7 @@ static void CopyToCSVOneRow(CopyToState cstate, TupleTableSlot *slot);
 static void CopyToTextLikeOneRow(CopyToState cstate, TupleTableSlot *slot,
 								 bool is_csv);
 static void CopyToTextLikeEnd(CopyToState cstate);
+static void CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot);
 static void CopyToBinaryStart(CopyToState cstate, TupleDesc tupDesc);
 static void CopyToBinaryOutFunc(CopyToState cstate, Oid atttypid, FmgrInfo *finfo);
 static void CopyToBinaryOneRow(CopyToState cstate, TupleTableSlot *slot);
@@ -148,9 +153,6 @@ static void CopySendInt16(CopyToState cstate, int16 val);
 
 /*
  * COPY TO routines for built-in formats.
- *
- * CSV and text formats share the same TextLike routines except for the
- * one-row callback.
  */
 
 /* text format */
@@ -169,6 +171,14 @@ static const CopyToRoutine CopyToRoutineCSV = {
 	.CopyToEnd = CopyToTextLikeEnd,
 };
 
+/* json format */
+static const CopyToRoutine CopyToRoutineJson = {
+	.CopyToStart = CopyToTextLikeStart,
+	.CopyToOutFunc = CopyToTextLikeOutFunc,
+	.CopyToOneRow = CopyToJsonOneRow,
+	.CopyToEnd = CopyToTextLikeEnd,
+};
+
 /* binary format */
 static const CopyToRoutine CopyToRoutineBinary = {
 	.CopyToStart = CopyToBinaryStart,
@@ -185,12 +195,14 @@ CopyToGetRoutine(const CopyFormatOptions *opts)
 		return &CopyToRoutineCSV;
 	else if (opts->format == COPY_FORMAT_BINARY)
 		return &CopyToRoutineBinary;
+	else if (opts->format == COPY_FORMAT_JSON)
+		return &CopyToRoutineJson;
 
 	/* default is text */
 	return &CopyToRoutineText;
 }
 
-/* Implementation of the start callback for text and CSV formats */
+/* Implementation of the start callback for text, CSV, and json formats */
 static void
 CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 {
@@ -209,6 +221,8 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 		ListCell   *cur;
 		bool		hdr_delim = false;
 
+		Assert(cstate->opts.format != COPY_FORMAT_JSON);
+
 		foreach(cur, cstate->attnumlist)
 		{
 			int			attnum = lfirst_int(cur);
@@ -231,7 +245,7 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 }
 
 /*
- * Implementation of the outfunc callback for text and CSV formats. Assign
+ * Implementation of the outfunc callback for text, CSV, and json formats. Assign
  * the output function data to the given *finfo.
  */
 static void
@@ -304,13 +318,38 @@ CopyToTextLikeOneRow(CopyToState cstate,
 	CopySendTextLikeEndOfRow(cstate);
 }
 
-/* Implementation of the end callback for text and CSV formats */
+/* Implementation of the end callback for text, CSV, and json formats */
 static void
 CopyToTextLikeEnd(CopyToState cstate)
 {
 	/* Nothing to do here */
 }
 
+/* Implementation of per-row callback for json format */
+static void
+CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
+{
+	Datum		rowdata;
+
+	/*
+	 * composite_to_json() requires a stable TupleDesc. The slot's descriptor
+	 * (slot->tts_tupleDescriptor) may change during the execution of a SELECT
+	 * query, using cstate->queryDesc instead. No need worry this if COPY TO
+	 * is directly from a table.
+	 */
+	if (!cstate->rel)
+		slot->tts_tupleDescriptor = cstate->queryDesc->tupDesc;
+
+	resetStringInfo(cstate->json_buf);
+
+	rowdata = ExecFetchSlotHeapTupleDatum(slot);
+	composite_to_json(rowdata, cstate->json_buf, false);
+
+	CopySendData(cstate, cstate->json_buf->data, cstate->json_buf->len);
+
+	CopySendTextLikeEndOfRow(cstate);
+}
+
 /*
  * Implementation of the start callback for binary format. Send a header
  * for a binary copy.
@@ -402,9 +441,23 @@ SendCopyBegin(CopyToState cstate)
 
 	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
 	pq_sendbyte(&buf, format);	/* overall format */
-	pq_sendint16(&buf, natts);
-	for (i = 0; i < natts; i++)
-		pq_sendint16(&buf, format); /* per-column formats */
+	if (cstate->opts.format != COPY_FORMAT_JSON)
+	{
+		pq_sendint16(&buf, natts);
+		for (i = 0; i < natts; i++)
+			pq_sendint16(&buf, format); /* per-column formats */
+	}
+	else
+	{
+		/*
+		 * For JSON format, report one text-format column.  Each CopyData
+		 * message contains one complete JSON object, not individual column
+		 * values, so the per-column count is always 1.
+		 */
+		pq_sendint16(&buf, 1);
+		pq_sendint16(&buf, 0);
+	}
+
 	pq_endmessage(&buf);
 	cstate->copy_dest = COPY_FRONTEND;
 }
@@ -506,7 +559,7 @@ CopySendEndOfRow(CopyToState cstate)
 }
 
 /*
- * Wrapper function of CopySendEndOfRow for text and CSV formats. Sends the
+ * Wrapper function of CopySendEndOfRow for text, CSV, and json formats. Sends the
  * line termination and do common appropriate things for the end of row.
  */
 static inline void
@@ -885,11 +938,23 @@ BeginCopyTo(ParseState *pstate,
 		ExecutorStart(cstate->queryDesc, 0);
 
 		tupDesc = cstate->queryDesc->tupDesc;
+		tupDesc = BlessTupleDesc(tupDesc);
 	}
 
 	/* Generate or convert list of attributes to process */
 	cstate->attnumlist = CopyGetAttnums(tupDesc, cstate->rel, attnamelist);
 
+	/* JSON outputs whole rows; a column list doesn't make sense */
+	if (cstate->opts.format == COPY_FORMAT_JSON)
+	{
+		cstate->json_buf = makeStringInfo();
+
+		if (attnamelist != NIL)
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("column selection is not supported in JSON mode"));
+	}
+
 	num_phys_attrs = tupDesc->natts;
 
 	/* Convert FORCE_QUOTE name list to per-column flags, check validity */
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3c3e24324a8..40ad9073901 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3612,6 +3612,10 @@ copy_opt_item:
 				{
 					$$ = makeDefElem("format", (Node *) makeString("csv"), @1);
 				}
+			| JSON
+				{
+					$$ = makeDefElem("format", (Node *) makeString("json"), @1);
+				}
 			| HEADER_P
 				{
 					$$ = makeDefElem("header", (Node *) makeBoolean(true), @1);
@@ -3694,6 +3698,10 @@ copy_generic_opt_elem:
 				{
 					$$ = makeDefElem($1, $2, @1);
 				}
+			| FORMAT_LA copy_generic_opt_arg
+				{
+					$$ = makeDefElem("format", $2, @1);
+				}
 		;
 
 copy_generic_opt_arg:
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 0b161398465..f609d7b9417 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -86,8 +86,6 @@ typedef struct JsonAggState
 	JsonUniqueBuilderState unique_check;
 } JsonAggState;
 
-static void composite_to_json(Datum composite, StringInfo result,
-							  bool use_line_feeds);
 static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
 							  const Datum *vals, const bool *nulls, int *valcount,
 							  JsonTypeCategory tcategory, Oid outfuncoid,
@@ -517,8 +515,9 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
 
 /*
  * Turn a composite / record into JSON.
+ * Exported so COPY TO can use it.
  */
-static void
+void
 composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 {
 	HeapTupleHeader td;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index f8c0865ca89..0d9649c1f0a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3425,7 +3425,7 @@ match_previous_words(int pattern_id,
 
 			/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 			else if (TailMatches("FORMAT"))
-				COMPLETE_WITH("binary", "csv", "text");
+				COMPLETE_WITH("binary", "csv", "text", "json");
 
 			/* Complete COPY <sth> FROM|TO filename WITH (FREEZE */
 			else if (TailMatches("FREEZE"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 2430fb0b2e5..2b5bef6738e 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -57,6 +57,7 @@ typedef enum CopyFormat
 	COPY_FORMAT_TEXT = 0,
 	COPY_FORMAT_BINARY,
 	COPY_FORMAT_CSV,
+	COPY_FORMAT_JSON,
 } CopyFormat;
 
 /*
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index f8cc52b1e78..2f4be40518d 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -17,6 +17,8 @@
 #include "lib/stringinfo.h"
 
 /* functions in json.c */
+extern void composite_to_json(Datum composite, StringInfo result,
+							  bool use_line_feeds);
 extern void escape_json(StringInfo buf, const char *str);
 extern void escape_json_with_len(StringInfo buf, const char *str, int len);
 extern void escape_json_text(StringInfo buf, const text *txt);
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index d0d563e0fa8..4324e3e4961 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -73,6 +73,92 @@ copy copytest3 to stdout csv header;
 c1,"col with , comma","col with "" quote"
 1,a,1
 2,b,2
+--- test copying in JSON mode with various styles
+copy (select 1 union all select 2) to stdout with (format json);
+{"?column?":1}
+{"?column?":2}
+copy (values (1), (2)) TO stdout with (format json);
+{"column1":1}
+{"column1":2}
+copy copytest to stdout json;
+{"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);
+{"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}
+-- all of the following should yield error
+copy copytest to stdout (format json, delimiter '|');
+ERROR:  cannot specify DELIMITER in JSON mode
+copy copytest to stdout (format json, null '\N');
+ERROR:  cannot specify NULL in JSON mode
+copy copytest to stdout (format json, default '|');
+ERROR:  cannot specify DEFAULT in JSON mode
+copy copytest to stdout (format json, header);
+ERROR:  cannot specify HEADER in JSON mode
+copy copytest to stdout (format json, header 1);
+ERROR:  cannot specify HEADER in JSON mode
+copy copytest to stdout (format json, quote '"');
+ERROR:  COPY QUOTE requires CSV mode
+copy copytest to stdout (format json, escape '"');
+ERROR:  COPY ESCAPE requires CSV mode
+copy copytest to stdout (format json, force_quote *);
+ERROR:  COPY FORCE_QUOTE requires CSV mode
+copy copytest to stdout (format json, force_not_null *);
+ERROR:  COPY FORCE_NOT_NULL requires CSV mode
+copy copytest to stdout (format json, force_null *);
+ERROR:  COPY FORCE_NULL requires CSV mode
+copy copytest to stdout (format json, on_error ignore);
+ERROR:  COPY ON_ERROR cannot be used with COPY TO
+LINE 1: copy copytest to stdout (format json, on_error ignore);
+                                              ^
+copy copytest to stdout (format json, reject_limit 1);
+ERROR:  COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
+copy copytest from stdin(format json);
+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
+-- embedded escaped characters
+create temp table copyjsontest (
+    id bigserial,
+    f1 text,
+    f2 timestamptz);
+insert into copyjsontest
+  select g.i,
+         CASE WHEN g.i % 2 = 0 THEN
+           'line with '' in it: ' || g.i::text
+         ELSE
+           'line with " in it: ' || g.i::text
+         END,
+         'Mon Feb 10 17:32:01 1997 PST'
+  from generate_series(1,5) as g(i);
+insert into copyjsontest (f1) values
+(E'aaa\"bbb'::text),
+(E'aaa\\bbb'::text),
+(E'aaa\/bbb'::text),
+(E'aaa\bbbb'::text),
+(E'aaa\fbbb'::text),
+(E'aaa\nbbb'::text),
+(E'aaa\rbbb'::text),
+(E'aaa\tbbb'::text);
+copy copyjsontest to stdout json;
+{"id":1,"f1":"line with \" in it: 1","f2":"1997-02-10T17:32:01-08:00"}
+{"id":2,"f1":"line with ' in it: 2","f2":"1997-02-10T17:32:01-08:00"}
+{"id":3,"f1":"line with \" in it: 3","f2":"1997-02-10T17:32:01-08:00"}
+{"id":4,"f1":"line with ' in it: 4","f2":"1997-02-10T17:32:01-08:00"}
+{"id":5,"f1":"line with \" in it: 5","f2":"1997-02-10T17:32:01-08:00"}
+{"id":1,"f1":"aaa\"bbb","f2":null}
+{"id":2,"f1":"aaa\\bbb","f2":null}
+{"id":3,"f1":"aaa/bbb","f2":null}
+{"id":4,"f1":"aaa\bbbb","f2":null}
+{"id":5,"f1":"aaa\fbbb","f2":null}
+{"id":6,"f1":"aaa\nbbb","f2":null}
+{"id":7,"f1":"aaa\rbbb","f2":null}
+{"id":8,"f1":"aaa\tbbb","f2":null}
 create temp table copytest4 (
 	c1 int,
 	"colname with tab: 	" text);
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 65cbdaf7f3e..4e9f74537f8 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -82,6 +82,57 @@ this is just a line full of junk that would error out if parsed
 
 copy copytest3 to stdout csv header;
 
+--- test copying in JSON mode with various styles
+copy (select 1 union all select 2) to stdout with (format json);
+copy (values (1), (2)) TO stdout with (format json);
+copy copytest to stdout json;
+copy copytest to stdout (format json);
+
+-- all of the following should yield error
+copy copytest to stdout (format json, delimiter '|');
+copy copytest to stdout (format json, null '\N');
+copy copytest to stdout (format json, default '|');
+copy copytest to stdout (format json, header);
+copy copytest to stdout (format json, header 1);
+copy copytest to stdout (format json, quote '"');
+copy copytest to stdout (format json, escape '"');
+copy copytest to stdout (format json, force_quote *);
+copy copytest to stdout (format json, force_not_null *);
+copy copytest to stdout (format json, force_null *);
+copy copytest to stdout (format json, on_error ignore);
+copy copytest to stdout (format json, reject_limit 1);
+copy copytest from stdin(format json);
+copy copytest (style) to stdout (format json);
+-- all of the above should yield error
+
+-- embedded escaped characters
+create temp table copyjsontest (
+    id bigserial,
+    f1 text,
+    f2 timestamptz);
+
+insert into copyjsontest
+  select g.i,
+         CASE WHEN g.i % 2 = 0 THEN
+           'line with '' in it: ' || g.i::text
+         ELSE
+           'line with " in it: ' || g.i::text
+         END,
+         'Mon Feb 10 17:32:01 1997 PST'
+  from generate_series(1,5) as g(i);
+
+insert into copyjsontest (f1) values
+(E'aaa\"bbb'::text),
+(E'aaa\\bbb'::text),
+(E'aaa\/bbb'::text),
+(E'aaa\bbbb'::text),
+(E'aaa\fbbb'::text),
+(E'aaa\nbbb'::text),
+(E'aaa\rbbb'::text),
+(E'aaa\tbbb'::text);
+
+copy copyjsontest to stdout json;
+
 create temp table copytest4 (
 	c1 int,
 	"colname with tab: 	" text);
-- 
2.43.0