On Sep 16, 2023, at 18:13, Erik Wienhold <ewie@ewie.name> wrote:
> Looks like the effect of lax mode which may unwrap arrays when necessary [1].
> The array unwrapping looks like the result of jsonb_array_elements().
>
> It kinda works in strict mode:
>
> SELECT jsonb_path_query(:'json', 'strict $.track.segments[*].location ? (@[*] < 14)');
>
> jsonb_path_query
> -----------------------
> [47.763, 13.4034]
> [47.706, 13.2635]
> (2 rows)
>
> But it does not remove elements from the matching arrays. Which I don't even
> expect here because the path specifies the location array as the object to be
> returned. The filter expression then only decides whether to return the
> location array or not. Nowhere in the docs does it say that the filter
> expression itself removes any elements from a matched array.
Yes, this is what I expected. It means “select the location array if any of its contents is less that 14.”
I don’t understand why it’s different in lax mode, though, as `@[*]` is not a structural error; it confirms to the
schema,as the docs say. The flattening in this case seems weird.
Ah, here’s why:, from the docs:
"Besides, comparison operators automatically unwrap their operands in the lax mode, so you can compare SQL/JSON arrays
out-of-the-box.”
There follow some discussion of the need to specify `[*]` on segments in strict mode, but since that’s exactly what my
exampledoes (and the same for the locations array inside the filter), it doesn’t seem right to me that it would be
unwrappedhere.
> Here's a query that filter's out individual array elements. It's quite a
> mouthful (especially to preserve the order of array elements):
Wow fun, and yeah, it makes sense to take things apart in SQL for this sort of thing!
Best,
David