Обсуждение: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

declare
  j1          constant jsonb   not null := '{"x": 42, "y": null}';
  j2          constant jsonb   not null := '{"x": 42           }';
... 
(j1->>'y' is null)

 
This produces a JSON Null which when asked for as a text data type results into SQL NULL due to implicit type coercion

(j2->>'y' is null)

This produces "key y not present in JSON" but someone decided that was too unfriendly and so we instead produce SQL NULL. This SQL NULL exists for a different reason than the SQL NULL in the previous case.  Unfortunately, there is only a single concept of NULL in the SQL Language.

  predicate_2 constant boolean not null := j1 = j2;

This is trivially false, j1 has a key of "y" while j2 does not. If there is a complaint to be had, this returning false isn't it.

Another test (I won't bore you with its code) confirms that the order in which the incoming document lists key-value pairs has no consequence for its meaning.

Yes, the equality test is semantic, do all the same keys and corresponding values exist?  If so, return true.

  j1_in  constant jsonb not null := '{"a": 17, "b": null}';
  j2_in  constant jsonb not null := '{"a": 17           }';

This is the result:

{"a": 17, "b": null}
{"a": 17, "b": null}

Yep, for basically the same reason as the first point.  The nulls are consequences of different situations (lack of key, value of key being null) being largely undistinguishable at the SQL level. We provide a "does key exists" operator if you do need to make that determination.

I have a little demo where I shred a set of "books" incoming JSON documents (where a book has a genre and many authors) into the classic Codd-and-Date four tables: books, authors, genres, and book_author_intersections. Then I scrunch each book back to a single JSON doc. I want to prove that I get back what I started with.

Yeah, the lack of any goal of round tripping conversions from JSON through SQL and back into SQL makes proving that the system does such a thing problematic. You'll get a back something meaningfully equivalent, by your own argument, but not identical on a key-by-key basis.

Bryn Llewellyn <bryn@yugabyte.com> writes:
> I'm going to try to think like this:
> The number of possible spellings of the names of keys in a JSON object is some flavor of infinite. So including this
inan object: 
> "k": null
> really is saying something. It says that I do know about "k" and that yet I have simply no information available
aboutits value. 

I'd read it as asserting that key "k" is meaningful for this object,
but the correct value for that key is not known.

I have a hard time with your assertion that {"x": 42, "y": null}
should be considered equivalent to {"x": 42}, because it would
render key-exists predicates useless.  Either you have to say that
key "y" is claimed to exist in both of these objects and indeed every
object, or you have to make it fail if the key's value is null (so that
it'd say "false" in both of these cases).  Either of those options
seems both weird and useless.

> The quirkiness that my first example showed can be seen differently from how I saw it first. When I now consider this
expression:
> ('{"x": 42}'::jsonb)->>'y'
> it seems that its evaluation should simply raise an exception. But you said:
>> This produces "key y not present in JSON" but someone decided that was too unfriendly and so we instead produce SQL
NULL.

Right.  This is hard to justify from a purist semantic point of view,
but having the operator throw an error in such cases would make it
close to unusable on not-uniformly-structured data.  And really the
point of using JSON inside a SQL database is to cope with irregularly-
structured data, so fuzziness seems like what we want.

            regards, tom lane

On 2022-06-17 17:14:14 -0700, Bryn Llewellyn wrote:
> "isbn" — string
> values must be unique across the entire set of documents (in other words, it
> defines the unique business key); values must have this pattern:
>
>   « ^[0-9]{3}-[0-9]{1}-[0-9]{2}-[0-9]{6}-[0-9]{1}$ »

Completely off-topic, but this regexp doesn't describe ISBNs. In ISBNs
the three middle subfields are all variable length. The first is a
language code (there are more than 10 languages in the world), the
second identifies the publisher (there are more than 100 publishers) and
the third the book. For example, "Mastering PostgreSQL 9.6" has the ISBN
978-1-78355-535-2.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

> On 18 Jun 2022, at 2:14, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>
> I implemented two complementary functions:
>
> —"no_null_keys()" checks that a "jsonb" value has no occurrences of « "some key": null »
>
> —"strip_null_keys()" removes « "some key": null » occurrences from a "jsonb" value
>
> The code checks with "no_null_keys()" that, as expected, no ingested JSON document has an occurrence of « "some key":
null». 
>
> And it uses "strip_null_keys()" on the output of "to_jsonb()" — and, as appropriate, any other built-in JSON function
thatproduces a "jsonb" value. 
>
> It was straightforward to implement these two functions by using REGEXP built-in functionality on the canonically
formatted"text" value produced by the "jsonb::text" typecast. 

In my experience, using regular expressions applied to document formats tends to get you false positives. I’d be
worriedabout books with titles similar to 'How we wrote a regular expression to detect occurrences of "some key": null
inour JSON documents', for example. 

For stripping those null occurrences, you are aware of the json_strip_nulls(json) and jsonb_strip_nulls(jsonb)
functions,right? 

For detecting them on a recent PG, the @? operator or json_path_exists(json, jsonpath) functions would probably do the
trick.
I am not too familiar with JSONPATH expressions, but I expect (it passed some preliminary testing) this would detect
yournulls just fine, while taking JSON semantics into account: 

jsonb_path_exists(document, '$.** ? (@ == null)'::jsonpath)

For PG-specifics on JSONPATH, see section 9.16.2 on:
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-OP-TABLE

A recursive query is another possible solution. It would probably perform far worse, but I find them more rewarding to
write.Some people prefer Sodoku. 

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

haramrae@gmail.com wrote

bryn@yugabyte.com wrote:

I implemented two complementary functions:

—"no_null_keys()" checks that a "jsonb" value has no occurrences of « "some key": null »

—"strip_null_keys()" removes « "some key": null » occurrences from a "jsonb" value

The code checks with "no_null_keys()" that, as expected, no ingested JSON document has an occurrence of « "some key": null ».

And it uses "strip_null_keys()" on the output of "to_jsonb()" — and, as appropriate, any other built-in JSON function that produces a "jsonb" value.

It was straightforward to implement these two functions by using REGEXP built-in functionality on the canonically formatted "text" value produced by the "jsonb::text" typecast.

In my experience, using regular expressions applied to document formats tends to get you false positives. I’d be worried about books with titles similar to:

«

How we wrote a regular expression to detect occurrences of "some key": null in our JSON documents
»

For stripping those null occurrences, you are aware of the json_strip_nulls(json) and jsonb_strip_nulls(jsonb) functions, right?

For detecting them on a recent PG, the @? operator or json_path_exists(json, jsonpath) functions would probably do the trick.
I am not too familiar with JSONPATH expressions, but I expect (it passed some preliminary testing) this would detect your nulls just fine, while taking JSON semantics into account:

jsonb_path_exists(document, '$.** ? (@ == null)'::jsonpath)

For PG-specifics on JSONPATH, see section 9.16.2: 

https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-OP-TABLE

A recursive query is another possible solution. It would probably perform far worse...

jian.universality@gmail.com wrote:

bryn@yugabyte.com wrote:

/* ————— START OF SPEC —————————————————————————————— */

The document's top-level object may use only these keys:

...

And the meaning of *not required* is simply "no information is available for this key" (with no nuances). The spec author goes further by adding a rule: this meaning must be expressed by the absence of such a key.


/* ————— END OF SPEC ———————————————————————————————— */

create temp table source(
  ...
  CONSTRAINT test_jsonb_constraints1 check (book_info_text is json)
  CONSTRAINT test_jsonb_constraints2 check (JSON_EXISTS(book_info_text::jsonb,'$.title') )
  ...
  )
);

Some of the problems I don't know how to solve. My intuition feels like that isbn attribute in the json document column then enforcing the unique constraint would be anti-pattern. So I put the isbn outside as a separate column. Another constraint is that there are only certain keys in the jsonb. I don't know how to implement it. But I feel like it's do-able.
two columns, one text, another generated column stored jsonb, So there is a duplication issue...

So there is another alternative way to do it...

Bryn Llewellyn schrieb am 04.06.2022 um 03:41:
> Am I missing a use case where an object with a key-value pair with a
> JSON null value is meaningfully different from one where the key is
> simply absent?
It seems the JSON specification doesn't actually define equality.
But the JSON patch RFC 6902[1] defines the equality of two objects
as:

   objects: are considered equal if they contain the same number of
   members, and if each member can be considered equal to a member in
   the other object, by comparing their keys (as strings) and their
   values (using this list of type-specific rules).

As {"x": 42, "y": null} and {"x": 42} do not contain the same number
of members, I think Postgres' behaviour is correct.

Thomas


[1] https://www.rfc-editor.org/rfc/rfc6902#section-4.6

> shammat@gmx.net wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> Am I missing a use case where an object with a key-value pair with a JSON null value is meaningfully different from
onewhere the key is simply absent? 
>
> It seems the JSON specification doesn't actually define equality. But the JSON patch RFC 6902:
>
> www.rfc-editor.org/rfc/rfc6902#section-4.6
>
> defines the equality of two objects as:
>
> «
> objects: are considered equal if they contain the same number of members, and if each member can be considered equal
toa member in the other object, by comparing their keys (as strings) and their values (using this list of type-specific
rules)
> »
>
> As {"x": 42, "y": null} and {"x": 42} do not contain the same number of members, I think Postgres' behaviour is
correct.

Thanks. I believe that I addressed this in a separate branch in this thread. The distinction (between « "some key":
null» and "some key" absent), which by all means matters in some contexts, does not matter w.r.t. the requirements of
my(demo) app. And I've now proved (to myself, at least) that I can meet my goal easily with existing Postgres
features—especially"jsonb_strip_nulls()". 

I hope that this means that we're all happy.