Re: JSON Path and GIN Questions
От | David E. Wheeler |
---|---|
Тема | Re: JSON Path and GIN Questions |
Дата | |
Msg-id | BAF11F2D-5EDD-4DBB-87FA-4F35845029AE@justatheory.com обсуждение исходный текст |
Ответ на | Re: JSON Path and GIN Questions (Erik Wienhold <ewie@ewie.name>) |
Список | pgsql-hackers |
Thanks for the reply, Erik. Have appreciated collaborating with you on a few different things lately! > On Oct 13, 2023, at 22:50, Erik Wienhold <ewie@ewie.name> wrote: >> Hi, finally getting back to this, still fiddling to figure out the >> differences. From the thread you reference [1], is the point that @@ >> and jsonb_path_match() can only be properly used with a JSON Path >> expression that’s a predicate check? > > I think so. That's also supported by the existing docs which only > mention "JSON path predicate" for @@ and jsonb_path_match(). Okay, good. >> If so, as far as I can tell, only exists() around the entire path >> query, or the deviation from the SQL standard that allows an >> expression to be a predicate? > > Looks like that. But note that exists() is also a filter expression. > So wrapping the entire jsonpath in exists() is also a deviation from the > SQL standard which only allows predicates in filter expressions, i.e. > '<path> ? (<predicate>)'. Yeah. I’m starting to get the sense that the Postgres extension of the standard to allow predicates without filters is almosta different thing, like there are two Pg SQL/JSON Path languages: 1. SQL Standard path language for selecting values and includes predicates. Returns the selected value(s). Supported by `@?`and jsonb_path_exists(). 2. The Postgres predicate path language which returns a boolean, akin to a WHERE expression. Supported by `@@` and jsonb_path_match() Both are supported by jsonb_path_query(), but if you use a standard path you get the values and if you use a predicate pathyou get a boolean. This feels a big overloaded to me, TBH; I find myself wanting them to be separate types since thebehaviors vary quite a bit! >> This suggest to me that the "Only the first item of the result is >> taken into account” bit from the docs may not be quite right. > > Yes, this was also the issue in the referenced thread[1]. I think my > suggesstion in [2] explains it (as far as I understand it). Yeah, lax vs. strict mode stuff definitely creates some added complexity. I see now I missed the rest of that thread; seeingthe entire thread on one page[1] really helps. I’d like to take a stab at the doc improvements Tom suggests[2]. >> jsonb_path_match(), it turns out, only wants a single result. But >> furthermore perhaps the use of a filter predicate rather than a >> predicate expression for the entire path query is an error? > > Yes, I think @@ and jsonb_path_match() should not be used with filter > expressions because the jsonpath returns whatever the path expression > yields (which may be an actual boolean value in the jsonb). The filter > expression only filters (as the name suggests) what the path expression > yields. Agreed. It only gets worse with a filter expression that selects a single value: david=# select jsonb_path_match('{"a":[false,true]}', '$.a ?(@[*] == false)'); jsonb_path_match ------------------ f Presumably it returns false because the value selected is JSON `false`: david=# select jsonb_path_query('{"a":[false,true]}', '$.a ?(@[*] == false)'); jsonb_path_query ------------------ false Which seems misleading, frankly. Would it be possible to update jsonb_path_match and @@ to raise an error when the path expressionis not a predicate? >> Curiously, @@ seems okay with it: >> >> david=# select '{"a":[false,true,false]}'@@ '$.a ?(@[*] == false)'; >> ?column? >> ---------- >> t >> >> Not a predicate query, and somehow returns true even though the first >> item of the result is false? Is that how it should be? > > Your example does a text search equivalent to: > > select to_tsvector('{"a":[false,true,false]}') @@ plainto_tsquery('$.a ? (@[*] == true)') > > You forgot the cast to jsonb. Oh good grief 🤦🏻♂️ > jsonb @@ jsonpath actually returns null: > > test=# select '{"a":[false,true,false]}'::jsonb @@ '$.a ? (@[*] == false)'; > ?column? > ---------- > <null> > (1 row) Yes, much better, though see the result above that returns a single `false` and confuses things. > This matches the note right after the docs for @@: Yeah, that makes sense. But here’s a bit about lax mode[3] that confuses me: > The lax mode facilitates matching of a JSON document structure and path expression if the JSON data does not conform tothe expected schema. If an operand does not match the requirements of a particular operation, it can be automatically wrappedas an SQL/JSON array or unwrapped by converting its elements into an SQL/JSON sequence before performing this operation.Besides, comparison operators automatically unwrap their operands in the lax mode, so you can compare SQL/JSONarrays out-of-the-box. This automatic flattening in lax mode seems odd, because it means you get different results in strict and lax mode wherethere are no errors. In lax mode, you get a set: david=# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a ?(@[*] > 2)'); jsonb_path_query ------------------ 3 4 5 (3 rows) But in strict mode, you get the array selected by `$.a`, which is more what I would expect: david=# select jsonb_path_query('{"a":[1,2,3,4,5]}', 'strict $.a ?(@[*] > 2)'); jsonb_path_query ------------------ [1, 2, 3, 4, 5] This seems like an odd inconsistency in return values, but perhaps the standard calls for this? I don’t have access to it,but MSSQL docs[4], at least, say: > * In **lax** mode, the function returns empty values if the path expression contains an error. For example, if you requestthe value **$.name**, and the JSON text doesn't contain a **name** key, the function returns null, but does not raisean error. > > * In **strict** mode, the function raises an error if the path expression contains an error. No flattening, only error suppression. The Oracle docs[5] mention array flattening, but I don’t have it up and running tosee if that means query *results* are flattened. Best, David [1] https://www.postgresql.org/message-id/flat/CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA%40mail.gmail.com [2] https://www.postgresql.org/message-id/1229727.1680535592%40sss.pgh.pa.us [3] https://www.postgresql.org/docs/current/functions-json.html#STRICT-AND-LAX-MODES [4] https://learn.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server?view=sql-server-ver16#PATHMODE [5] https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/json-path-expressions.html#GUID-8656CAB9-C293-4A99-BB62-F38F3CFC4C13
В списке pgsql-hackers по дате отправления:
Следующее
От: "Imseih (AWS), Sami"Дата:
Сообщение: Re: False "pg_serial": apparent wraparound” in logs