SQL/JSON: add standard JSON constructor functions

This commit introduces the SQL/JSON standard-conforming constructors for
JSON types:

JSON_ARRAY()
JSON_ARRAYAGG()
JSON_OBJECT()
JSON_OBJECTAGG()

Most of the functionality was already present in PostgreSQL-specific
functions, but these include some new functionality such as the ability
to skip or include NULL values, and to allow duplicate keys or throw
error when they are found, as well as the standard specified syntax to
specify output type and format.

Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Amit Langote <amitlangote09@gmail.com>

Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby.

Discussion: https://postgr.es/m/CAF4Au4w2x-5LTnN_bxky-mq4=WOqsGsxSpENCzHRAzSnEd8+WQ@mail.gmail.com
Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org

9 files changed, 349 insertions, 0 deletions

diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl
index 296cd7193c..faeb460ef5 100644
--- a/src/interfaces/ecpg/preproc/parse.pl
+++ b/src/interfaces/ecpg/preproc/parse.pl
@@ -55,9 +55,11 @@ my %replace_token = (
 
 # or in the block
 my %replace_string = (
+	'FORMAT_LA'      => 'format',
 	'NOT_LA'         => 'not',
 	'NULLS_LA'       => 'nulls',
 	'WITH_LA'        => 'with',
+	'WITHOUT_LA'     => 'without',
 	'TYPECAST'       => '::',
 	'DOT_DOT'        => '..',
 	'COLON_EQUALS'   => ':=',
diff --git a/src/interfaces/ecpg/preproc/parser.c b/src/interfaces/ecpg/preproc/parser.c
index f447dc5d84..a40f4bef09 100644
--- a/src/interfaces/ecpg/preproc/parser.c
+++ b/src/interfaces/ecpg/preproc/parser.c
@@ -78,9 +78,11 @@ filtered_base_yylex(void)
 	 */
 	switch (cur_token)
 	{
+		case FORMAT:
 		case NOT:
 		case NULLS_P:
 		case WITH:
+		case WITHOUT:
 		case UIDENT:
 		case USCONST:
 			break;
@@ -110,6 +112,16 @@ filtered_base_yylex(void)
 	/* Replace cur_token if needed, based on lookahead */
 	switch (cur_token)
 	{
+		case FORMAT:
+			/* Replace FORMAT by FORMAT_LA if it's followed by JSON */
+			switch (next_token)
+			{
+				case JSON:
+					cur_token = FORMAT_LA;
+					break;
+			}
+			break;
+
 		case NOT:
 			/* Replace NOT by NOT_LA if it's followed by BETWEEN, IN, etc */
 			switch (next_token)
@@ -145,6 +157,16 @@ filtered_base_yylex(void)
 					break;
 			}
 			break;
+
+		case WITHOUT:
+			/* Replace WITHOUT by WITHOUT_LA if it's followed by UNIQUE */
+			switch (next_token)
+			{
+				case UNIQUE:
+					cur_token = WITHOUT_LA;
+					break;
+			}
+			break;
 		case UIDENT:
 		case USCONST:
 			/* Look ahead for UESCAPE */
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index e034c5a420..39814a39c1 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -50,6 +50,7 @@ test: sql/indicators
 test: sql/oldexec
 test: sql/quote
 test: sql/show
+test: sql/sqljson
 test: sql/insupd
 test: sql/parser
 test: sql/prepareas
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
new file mode 100644
index 0000000000..64784542ed
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -0,0 +1,203 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "sqljson.pgc"
+#include <stdio.h>
+
+
+#line 1 "sqlca.h"
+#ifndef POSTGRES_SQLCA_H
+#define POSTGRES_SQLCA_H
+
+#ifndef PGDLLIMPORT
+#if  defined(WIN32) || defined(__CYGWIN__)
+#define PGDLLIMPORT __declspec (dllimport)
+#else
+#define PGDLLIMPORT
+#endif							/* __CYGWIN__ */
+#endif							/* PGDLLIMPORT */
+
+#define SQLERRMC_LEN	150
+
+#ifdef __cplusplus
+extern "C"
+{
+#endif
+
+struct sqlca_t
+{
+	char		sqlcaid[8];
+	long		sqlabc;
+	long		sqlcode;
+	struct
+	{
+		int			sqlerrml;
+		char		sqlerrmc[SQLERRMC_LEN];
+	}			sqlerrm;
+	char		sqlerrp[8];
+	long		sqlerrd[6];
+	/* Element 0: empty						*/
+	/* 1: OID of processed tuple if applicable			*/
+	/* 2: number of rows processed				*/
+	/* after an INSERT, UPDATE or				*/
+	/* DELETE statement					*/
+	/* 3: empty						*/
+	/* 4: empty						*/
+	/* 5: empty						*/
+	char		sqlwarn[8];
+	/* Element 0: set to 'W' if at least one other is 'W'	*/
+	/* 1: if 'W' at least one character string		*/
+	/* value was truncated when it was			*/
+	/* stored into a host variable.             */
+
+	/*
+	 * 2: if 'W' a (hopefully) non-fatal notice occurred
+	 */	/* 3: empty */
+	/* 4: empty						*/
+	/* 5: empty						*/
+	/* 6: empty						*/
+	/* 7: empty						*/
+
+	char		sqlstate[5];
+};
+
+struct sqlca_t *ECPGget_sqlca(void);
+
+#ifndef POSTGRES_ECPG_INTERNAL
+#define sqlca (*ECPGget_sqlca())
+#endif
+
+#ifdef __cplusplus
+}
+#endif
+
+#endif
+
+#line 3 "sqljson.pgc"
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 4 "sqljson.pgc"
+
+
+/* exec sql whenever sqlerror  sqlprint ; */
+#line 6 "sqljson.pgc"
+
+
+int
+main ()
+{
+/* exec sql begin declare section */
+   
+
+#line 12 "sqljson.pgc"
+ char json [ 1024 ] ;
+/* exec sql end declare section */
+#line 13 "sqljson.pgc"
+
+
+  ECPGdebug (1, stderr);
+
+  { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , NULL, 0); 
+#line 17 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 17 "sqljson.pgc"
+
+  { ECPGsetcommit(__LINE__, "on", NULL);
+#line 18 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 18 "sqljson.pgc"
+
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_object ( returning text )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 20 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 20 "sqljson.pgc"
+
+  printf("Found json=%s\n", json);
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_object ( returning text format json )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 23 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 23 "sqljson.pgc"
+
+  printf("Found json=%s\n", json);
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_array ( returning jsonb )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 26 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 26 "sqljson.pgc"
+
+  printf("Found json=%s\n", json);
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_array ( returning jsonb format json )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 29 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 29 "sqljson.pgc"
+
+  printf("Found json=%s\n", json);
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_object ( 1 : 1 , '1' : null with unique )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 32 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 32 "sqljson.pgc"
+
+  // error
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_object ( 1 : 1 , '2' : null , 1 : '2' absent on null without unique keys )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 35 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 35 "sqljson.pgc"
+
+  printf("Found json=%s\n", json);
+
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_object ( 1 : 1 , '2' : null absent on null without unique returning jsonb )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 38 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 38 "sqljson.pgc"
+
+  printf("Found json=%s\n", json);
+
+  { ECPGdisconnect(__LINE__, "CURRENT");
+#line 41 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 41 "sqljson.pgc"
+
+
+  return 0;
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
new file mode 100644
index 0000000000..907f773eb9
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -0,0 +1,69 @@
+[NO_PID]: ECPGdebug: set to 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg1_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGsetcommit on line 18: action "on"; connection "ecpg1_regression"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 20: query: select json_object ( returning text ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 20: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 20: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 20: RESULT: {} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 23: query: select json_object ( returning text format json ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 23: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 23: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 23: RESULT: {} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 26: query: select json_array ( returning jsonb ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 26: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 26: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_is_type_an_array on line 26: type (3802); C (1); array (no)
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 26: RESULT: [] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 29: query: select json_array ( returning jsonb format json ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 29: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 29: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 29: RESULT: [] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: query: select json_object ( 1 : 1 , '1' : null with unique ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 32: bad response - ERROR:  duplicate JSON key "1"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 22030 (sqlcode -400): duplicate JSON key "1" on line 32
+[NO_PID]: sqlca: code: -400, state: 22030
+SQL error: duplicate JSON key "1" on line 32
+[NO_PID]: ecpg_execute on line 35: query: select json_object ( 1 : 1 , '2' : null , 1 : '2' absent on null without unique keys ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 35: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_is_type_an_array on line 35: type (114); C (1); array (no)
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 35: RESULT: {"1" : 1, "1" : "2"} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: query: select json_object ( 1 : 1 , '2' : null absent on null without unique returning jsonb ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 38: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 38: RESULT: {"1": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection ecpg1_regression closed
+[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
new file mode 100644
index 0000000000..aae052a2b9
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -0,0 +1,6 @@
+Found json={}
+Found json={}
+Found json=[]
+Found json=[]
+Found json={"1" : 1, "1" : "2"}
+Found json={"1": 1}
diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile
index 876ca8df3e..d8213b25ce 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -23,6 +23,7 @@ TESTS = array array.c \
         parser parser.c \
         quote quote.c \
         show show.c \
+        sqljson sqljson.c \
         insupd insupd.c \
         twophase twophase.c \
         insupd insupd.c \
diff --git a/src/interfaces/ecpg/test/sql/meson.build b/src/interfaces/ecpg/test/sql/meson.build
index 5149a73810..f4c9418abb 100644
--- a/src/interfaces/ecpg/test/sql/meson.build
+++ b/src/interfaces/ecpg/test/sql/meson.build
@@ -25,6 +25,7 @@ pgc_files = [
   'quote',
   'show',
   'sqlda',
+  'sqljson',
   'twophase',
 ]
 
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
new file mode 100644
index 0000000000..6a582b5b10
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -0,0 +1,44 @@
+#include <stdio.h>
+
+EXEC SQL INCLUDE sqlca;
+exec sql include ../regression;
+
+EXEC SQL WHENEVER SQLERROR sqlprint;
+
+int
+main ()
+{
+EXEC SQL BEGIN DECLARE SECTION;
+  char json[1024];
+EXEC SQL END DECLARE SECTION;
+
+  ECPGdebug (1, stderr);
+
+  EXEC SQL CONNECT TO REGRESSDB1;
+  EXEC SQL SET AUTOCOMMIT = ON;
+
+  EXEC SQL SELECT JSON_OBJECT(RETURNING text) INTO :json;
+  printf("Found json=%s\n", json);
+
+  EXEC SQL SELECT JSON_OBJECT(RETURNING text FORMAT JSON) INTO :json;
+  printf("Found json=%s\n", json);
+
+  EXEC SQL SELECT JSON_ARRAY(RETURNING jsonb) INTO :json;
+  printf("Found json=%s\n", json);
+
+  EXEC SQL SELECT JSON_ARRAY(RETURNING jsonb FORMAT JSON) INTO :json;
+  printf("Found json=%s\n", json);
+
+  EXEC SQL SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE) INTO :json;
+  // error
+
+  EXEC SQL SELECT JSON_OBJECT(1: 1, '2': NULL, 1: '2' ABSENT ON NULL WITHOUT UNIQUE KEYS) INTO :json;
+  printf("Found json=%s\n", json);
+
+  EXEC SQL SELECT JSON_OBJECT(1: 1, '2': NULL ABSENT ON NULL WITHOUT UNIQUE RETURNING jsonb) INTO :json;
+  printf("Found json=%s\n", json);
+
+  EXEC SQL DISCONNECT;
+
+  return 0;
+}