Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvGa4-002vM6-81 for pgsql-docs@arkaria.postgresql.org; Thu, 20 Mar 2025 14:09:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tvGa2-002FSs-SI for pgsql-docs@arkaria.postgresql.org; Thu, 20 Mar 2025 14:09:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvGa2-002FSj-FC for pgsql-docs@lists.postgresql.org; Thu, 20 Mar 2025 14:09:06 +0000 Received: from mail-qt1-x82c.google.com ([2607:f8b0:4864:20::82c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tvGZz-00094G-0O for pgsql-docs@lists.postgresql.org; Thu, 20 Mar 2025 14:09:05 +0000 Received: by mail-qt1-x82c.google.com with SMTP id d75a77b69052e-47664364628so8762711cf.1 for ; Thu, 20 Mar 2025 07:09:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=equatoria-us.20230601.gappssmtp.com; s=20230601; t=1742479741; x=1743084541; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=bajUCVASzeZSqvN138mwpgtCZEWHXcJbah8QpBLYnIQ=; b=eqRgKhCJIBoiOPrj0N8rQcmBU0M0x4kFTMzTdA88uuu6YTlSMJN9ypg6Hs6ejRdfSX b38TkbOUbvkAQr2n8qhTKSlg2TsbvW0qhIujGnbeGAqb9afINGB4aJnI0KeACY4aLZxc a3d57w4TM1xb+caDQ3m62Ci6PfldRQgL6UeSXtROW3eooWGB1V2JBYFAsjnf9NiXzbyZ mJeYoTdTh8a24RR2VxFBnqAiLchwfDlzAPJqFuoKblXU9m6KtQHKJg7L2W9XquIFxr83 D4aFoTzVekN3R2KERhaGHNmtbvgvEZ5HntqQb3/kVHD465IRzzyq+RaVuVwHzp6RUu2g HXBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742479741; x=1743084541; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=bajUCVASzeZSqvN138mwpgtCZEWHXcJbah8QpBLYnIQ=; b=fCRdH7k1dPHdpf8G+L3QKU3/4T8sFYPg7ydmlZY5fM1VZz6M4iup/SDYRTXIAyP3f7 Cu+31HPd/jiLXGwxCJpaRG4iFRArvh4iMl1NMoeWmiiGIILy5ng8vfOSX7qLhSNUL0+K fD8ZIJtSrBfaSbbNkHcEf4y00PDlcEMbZLxuZrEmFOK2u1yqrC7r/IV79xFfhsqBljSo maxonuvY9yF7+SBSO8v1yxRlVP3QzwCPUmpTGcQKK41rLrjsJZc0hQGG0XK8dC904OYU rL+44rfG1mFDmJvffjZQYIo6REl3e5gd4/+DAHE65vPPbklJ+J57H7BM5OyzQhMmZL1j RliA== X-Forwarded-Encrypted: i=1; AJvYcCWquYZkxTD/QISALpAYJsHUSiuozui2WkzCHGbIfyNSUM9qvKFu5LaKtJrFpyO0lIM2aRmxJHIXirA3@lists.postgresql.org X-Gm-Message-State: AOJu0YwglLFfmRjJpGb4AtNO2j8blHr5JFEhijBf6Gtlhl5pJNVzHQeu 3XVDXGLBfem0dIKZXqSn9UH3hr0cXOtttKJ4PWYHTHeECmD7aQj26RIGoMTTNcG3PHhOyiNw7d1 EL/3TZNfcrSgt/gtnJ8woBD/M133vm6z4zNeLiA== X-Gm-Gg: ASbGncs0f0rJZeSmAgbeLH2xs85aNnrneakQ/kKZGSz1iyc1jsPOvTTzkjNp9BxOeAL xR4G48+h/+61cZ/WtVrRSCxZOWtETRjmGENpOePthxKAetK42kXKyheVLJV3mjIVDjqkUq2250f mjkwWPD8kMaaAoYVVU+av9zXwMCkKFdCSDtZMcVi8UE3DK98cs1lJ0oi+49w== X-Google-Smtp-Source: AGHT+IGqWeoEmC6bZksvM/b2DVpeZSqO3ymN8XcRtga3CxScV5+hBSfFAHiKlU4V8Y3GDbfkyGkH2mCMUCkVZpqbfu4= X-Received: by 2002:a05:622a:a08:b0:467:82b4:d7a1 with SMTP id d75a77b69052e-47710cacf04mr58365611cf.20.1742479740704; Thu, 20 Mar 2025 07:09:00 -0700 (PDT) MIME-Version: 1.0 References: <174238647361.682.12732328104350596711@wrigleys.postgresql.org> In-Reply-To: <174238647361.682.12732328104350596711@wrigleys.postgresql.org> From: Kirk Parker Date: Thu, 20 Mar 2025 07:08:50 -0700 X-Gm-Features: AQ5f1Jpr5Mzc_SaR4KcRXoW1QSPzJKNQiH8MvXLtKa_k-OBsCw1oXoJgxaVwoW4 Message-ID: Subject: Re: Ambiguity in IS JSON description and logic To: vavankaru@gmail.com, pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f92a310630c6adfc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f92a310630c6adfc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Mar 20, 2025 at 2:46=E2=80=AFAM PG Doc comments form wrote: > 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 (i= t > 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 i= f > 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 objec= ts > 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"= . > First, WITHOUT UNIQUE KEYS does not mean "confirm that there are duplicate keys", it's just a way of stating the default explicitly. In other words it means "w/o testing for duplicate keys". Thus IS JSON OBJECT and IS JSON OBJECT WITHOUT UNIQUE KEYS will both always return identical results on the same JSON expression. Secondly, the UNIQUE test is recursive; for objects maybe the meaning is intuitive, but for JSON arrays -- which don't have any concept of keys; JSON arrays are just ordered lists -- it means "does this array contain any embedded objects with duplicate keys". See: SELECT js, js IS JSON "json?", js IS JSON OBJECT "object?", js IS JSON OBJECT WITH UNIQUE KEYS "object w. UK?", js IS JSON OBJECT WITHOUT UNIQUE KEYS "object w/o UK?", js IS JSON ARRAY "array?", js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?", js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?" FROM (VALUES ('[{"a":1},{"b":2,"b":3}]'), -- expect t for array, array w/o UK ('[{"a":1},{"b":2,"c":3}]'), -- expect t for ALL array tests ('{"b":2,"b":3}'), -- expect t for object, object w/o UK ('{"c":2,"d":3}'), -- expect t for ALL object tests ('{"c":2,"d":{ "e": 0, "e": 1}}'), -- WITH UNIQUE is recursive for nested objects ('{"c":2,"d":{ "e": 0, "f": {"g":1,"g":2}}}'), -- no matter how deep ('[{"a":1},{"b":2,"c":{"d":1, "d":2}}]') -- and also tests arrays recursively for embedded objecs ) foo(js); A couple of side notes: 1. Your first data example is not JSON at all. It's helpful for this kind of test to include a plain IS JSON column, since any of the IS JSON X tests can fail for two reasons: (a) it's not JSON, or (b) it is JSON but it's not an X. 2. Curiously, the JSON spec itself is completely silent on the meaning of objects with duplicate keys. PostgreSQL is more helpful in this regard--the docs explicitly state that the last value is the one that is retained by JSONB and used in processing functions. --000000000000f92a310630c6adfc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, Mar 20,= 2025 at 2:46=E2=80=AFAM PG Doc comments form <noreply@postgresql.org> wrote:
The following documentation comment = has been logged on the website:

Page: https://www.postgresql.org/docs/17/funct= ions-json.html
Description:

On the manual page
https://www.postgresql.org/docs/current/f= unctions-json.html, in the Table
9.48. "SQL/JSON Testing Functions" there is a description of IS J= SON. It
includes the next sentence: "If WITH UNIQUE KEYS is specified, then an= y
object in the expression is also tested to see if it has duplicate keys.&qu= ot;
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<= br> 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 confirm= s
my reasoning:=C2=A0
SELECT
=C2=A0 =C2=A0 js.vl AS=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 "tested str",=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0

=C2=A0 =C2=A0 js.vl IS JSON OBJECT WITH UNIQUE KEYS AS=C2=A0 =C2=A0 =C2=A0 = ".. object w. UQ keys",=C2=A0 =C2=A0

=C2=A0 =C2=A0 js.vl IS JSON OBJECT WITHOUT UNIQUE KEYS AS=C2=A0 =C2=A0"= ;.. object w/o UQ keys",
=C2=A0 =C2=A0 js.vl IS JSON ARRAY WITH UNIQUE KEYS AS=C2=A0 =C2=A0 =C2=A0 = =C2=A0".. array w. UQ keys",=C2=A0 =C2=A0

=C2=A0 =C2=A0 js.vl IS JSON ARRAY WITHOUT UNIQUE KEYS AS=C2=A0 =C2=A0 "= ;.. array w/o UQ keys",
=C2=A0 =C2=A0 js.vl IS JSON ARRAY ".. array"
FROM (VALUES ('{{"a": "a1"}, {"a": "= a2"}}'), ('[{"a": "a1"}, {"a": &= quot;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<= br> 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.=C2=A0 I tested it on
"PostgreSQL 17.0 on x86_64-windows, compiled by msvc-19.41.34120, 64-b= it".

First, WITHOUT UNIQUE KEYS do= es not mean "confirm that there are duplicate keys", it's jus= t a way of stating the default explicitly. In other words it means "w/= o testing for duplicate keys". Thus IS JSON OBJECT and IS JSON OBJECT = WITHOUT UNIQUE KEYS will both always return identical results on the same J= SON expression.=C2=A0

Secondly, the UNIQUE test is= recursive; for objects maybe the meaning is intuitive, but for JSON arrays= -- which don't have any concept of keys; JSON arrays are just ordered = lists -- it means "does this array contain any embedded objects with d= uplicate keys".

See:
=C2=A0SELECT js,
=C2=A0 js IS JSON "json?",
<= div class=3D"gmail_quote gmail_quote_container">
=C2=A0 js IS JSON OBJECT "object?",
=C2= =A0 js IS JSON OBJECT WITH UNIQUE KEYS "object w. UK?",
=C2=A0 js IS JSON OBJECT WITHOUT UNIQUE KEYS "object w/= o UK?",
=C2=A0 js IS JSON ARRAY "array?&quo= t;,
=C2=A0 js IS JSON ARRAY WITH UNIQUE KEYS "ar= ray w. UK?",
=C2=A0 js IS JSON ARRAY WITHOUT UNI= QUE KEYS "array w/o UK?"
FROM (VALUES
=C2=A0 ('[{"a":1},{"b":2,"b&q= uot;:3}]'), =C2=A0 =C2=A0 =C2=A0 -- expect t for array, array w/o UK
=C2=A0 ('[{"a":1},{"b":2,"= c":3}]'), =C2=A0 =C2=A0 =C2=A0 -- expect t for ALL array tests
=C2=A0 ('{"b":2,"b":3}'), = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- expect t for obj= ect, object w/o UK
=C2=A0 ('{"c":2,&quo= t;d":3}'), =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= -- expect t for ALL object tests
=C2=A0 ('{"= ;c":2,"d":{ "e": 0, "e": 1}}'), -- W= ITH UNIQUE is recursive for nested objects
=C2=A0 (&#= 39;{"c":2,"d":{ "e": 0, "f": {"= ;g":1,"g":2}}}'), -- no matter how deep
=C2=A0 ('[{"a":1},{"b":2,"c":{"= ;d":1, "d":2}}]') -- and also tests arrays recursively f= or embedded objecs
) foo(js);

A couple of side notes:

1. Your = first data example is not JSON at all.=C2=A0 It's helpful for this kind= of test to include a plain IS JSON column, since any of the IS JSON X test= s can fail for two reasons: (a) it's not JSON, or (b) it is JSON but it= 's not an X.

2. Curiously, the JSON spec = itself is completely silent on the meaning of objects with duplicate=C2=A0k= eys.=C2=A0=C2=A0PostgreSQL is more helpful in th= is regard--the docs explicitly state that the last value is the one that is= retained by JSONB and used in processing functions.

<= /div>

--000000000000f92a310630c6adfc--