From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | vavankaru(at)gmail(dot)com |
Subject: | Ambiguity in IS JSON description and logic |
Date: | 2025-03-19 12:14:33 |
Message-ID: | 174238647361.682.12732328104350596711@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/functions-json.html
Description:
On the manual page
https://www.postgresql.org/docs/current/functions-json.html, in the Table
9.48. "SQL/JSON Testing Functions" there is a description of IS JSON. It
includes the next sentence: "If WITH UNIQUE KEYS is specified, then any
object in the expression is also tested to see if it has duplicate keys."
And such text is ambiguous, because the term "object" has certain meaning
regarding json format. In reality the option WITH UNIQUE KEYS allows to
check for duplicated keys any array element not object. For objects, both
WITH UNIQUE KEYS and WITHOUT UNIQUE KEYS return false, and both IS JSON
ARRAY WITH UNIQUE KEY and IS JSON ARRAY WITHOUT UNIQUE KEY return true (it
is at the same time with and without unique values, how it is possible?),
i.e. it works the same as just IS JSON ARRAY. The example code that confirms
my reasoning:
SELECT
js.vl AS "tested str",
js.vl IS JSON OBJECT WITH UNIQUE KEYS AS ".. object w. UQ keys",
js.vl IS JSON OBJECT WITHOUT UNIQUE KEYS AS ".. object w/o UQ keys",
js.vl IS JSON ARRAY WITH UNIQUE KEYS AS ".. array w. UQ keys",
js.vl IS JSON ARRAY WITHOUT UNIQUE KEYS AS ".. array w/o UQ keys",
js.vl IS JSON ARRAY ".. array"
FROM (VALUES ('{{"a": "a1"}, {"a": "a2"}}'), ('[{"a": "a1"}, {"a": "a2"}]'),
('["a", "a"]')) AS js(vl);
I'm not sure what should be the right logic for this option, for me it looks
now the same as simple IS JSON ARRAY without any UNIQUE KEY option, but if
we use an option it should be either true for WITH UNIQUE KEYS or WITHOUT
UNIQUE KEYS but not for both at the same time. But anyway the sentence I
showed above should contain "array" instead of "object" because for objects
it returns false independently of applied option. I tested it on
"PostgreSQL 17.0 on x86_64-windows, compiled by msvc-19.41.34120, 64-bit".
From | Date | Subject | |
---|---|---|---|
Next Message | David Fiedler | 2025-03-19 14:03:34 | WHEN SQLSTATE '00000' THEN equals to WHEN OTHERS THEN |
Previous Message | Bruce Momjian | 2025-03-17 21:14:24 | Re: Binary Installation Docs |