SQL/JSON: support the IS JSON predicate

This patch introduces the SQL standard IS JSON predicate. It operates
on text and bytea values representing JSON, as well as on the json and
jsonb types. Each test has IS and IS NOT variants and supports a WITH
UNIQUE KEYS flag. The tests are:

IS JSON [VALUE]
IS JSON ARRAY
IS JSON OBJECT
IS JSON SCALAR

These should be self-explanatory.

The WITH UNIQUE KEYS flag makes these return false when duplicate keys
exist in any object within the value, not necessarily directly contained
in the outermost object.

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>
Author: Andrew Dunstan <andrew@dunslane.net>

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

1 files changed, 50 insertions, 21 deletions

diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index 64784542ed6..a2c49b54f99 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -100,103 +100,132 @@ main ()
 {
 /* exec sql begin declare section */
    
+   
 
 #line 12 "sqljson.pgc"
  char json [ 1024 ] ;
-/* exec sql end declare section */
+ 
 #line 13 "sqljson.pgc"
+ bool is_json [ 8 ] ;
+/* exec sql end declare section */
+#line 14 "sqljson.pgc"
 
 
   ECPGdebug (1, stderr);
 
   { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , NULL, 0); 
-#line 17 "sqljson.pgc"
+#line 18 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 17 "sqljson.pgc"
+#line 18 "sqljson.pgc"
 
   { ECPGsetcommit(__LINE__, "on", NULL);
-#line 18 "sqljson.pgc"
+#line 19 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 18 "sqljson.pgc"
+#line 19 "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"
+#line 21 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 20 "sqljson.pgc"
+#line 21 "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"
+#line 24 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 23 "sqljson.pgc"
+#line 24 "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"
+#line 27 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 26 "sqljson.pgc"
+#line 27 "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"
+#line 30 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 29 "sqljson.pgc"
+#line 30 "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"
+#line 33 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 32 "sqljson.pgc"
+#line 33 "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"
+#line 36 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 35 "sqljson.pgc"
+#line 36 "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"
+#line 39 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 38 "sqljson.pgc"
+#line 39 "sqljson.pgc"
 
   printf("Found json=%s\n", json);
 
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "with val ( js ) as ( values ( '{ \"a\": 1, \"b\": [{ \"a\": 1, \"b\": 0, \"a\": 2 }] }' ) ) select js is json \"IS JSON\" , js is not json \"IS NOT JSON\" , js is json value \"IS VALUE\" , js is json object \"IS OBJECT\" , js is json array \"IS ARRAY\" , js is json scalar \"IS SCALAR\" , js is json without unique keys \"WITHOUT UNIQUE\" , js is json with unique keys \"WITH UNIQUE\" from val", ECPGt_EOIT, 
+	ECPGt_bool,&(is_json[0]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[1]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[2]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[3]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[4]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[5]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[6]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_bool,&(is_json[7]),(long)1,(long)1,sizeof(bool), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 54 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 54 "sqljson.pgc"
+
+	  for (int i = 0; i < sizeof(is_json); i++)
+		  printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
+
   { ECPGdisconnect(__LINE__, "CURRENT");
-#line 41 "sqljson.pgc"
+#line 58 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 41 "sqljson.pgc"
+#line 58 "sqljson.pgc"
 
 
   return 0;