diff options
| author | Alvaro Herrera <alvherre@alvh.no-ip.org> | 2023-03-31 22:34:04 +0200 |
|---|---|---|
| committer | Alvaro Herrera <alvherre@alvh.no-ip.org> | 2023-03-31 22:34:04 +0200 |
| commit | 6ee30209a6f161d0a267a33f090c70c579c87c00 (patch) | |
| tree | eda2b3a9f0a61f3fc484819b39abf1eb130e0d88 /src/interfaces/ecpg/test/sql/sqljson.pgc | |
| parent | a2a0c7c29e47f39da905577659e66b0086b769cc (diff) | |
| download | postgresql-6ee30209a6f161d0a267a33f090c70c579c87c00.tar.gz postgresql-6ee30209a6f161d0a267a33f090c70c579c87c00.zip | |
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
Diffstat (limited to 'src/interfaces/ecpg/test/sql/sqljson.pgc')
| -rw-r--r-- | src/interfaces/ecpg/test/sql/sqljson.pgc | 17 |
1 files changed, 17 insertions, 0 deletions
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc index 6a582b5b105..a0055038344 100644 --- a/src/interfaces/ecpg/test/sql/sqljson.pgc +++ b/src/interfaces/ecpg/test/sql/sqljson.pgc @@ -10,6 +10,7 @@ main () { EXEC SQL BEGIN DECLARE SECTION; char json[1024]; + bool is_json[8]; EXEC SQL END DECLARE SECTION; ECPGdebug (1, stderr); @@ -38,6 +39,22 @@ EXEC SQL END DECLARE SECTION; 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 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" + INTO :is_json[0], :is_json[1], :is_json[2], :is_json[3], :is_json[4], + :is_json[5], :is_json[6], :is_json[7] + FROM val; + for (int i = 0; i < sizeof(is_json); i++) + printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false"); + EXEC SQL DISCONNECT; return 0; |