jsonpath with @@ and iterating over arrays

From: Thomas Kellerer <shammat(at)gmx(dot)net>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: jsonpath with @@ and iterating over arrays
Date: 2021-03-09 12:55:20
Message-ID: 1c0d4ed1-7a05-b524-6336-dcc7a72f1be9@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a question regarding the processing of arrays when using the "short jsonpath"
with the @@ operator.

E.g. when trying to "translate" this jsonpath

where jsonb_path_exists(the_column, '$[*] ? (@.id == 42 && @.type == "foo")')

to be used with the @@ operator, then the "? (...)" cannot be used.

The following seems to work

where the_column @@ '$[*].id == 42 && $[*].type == "foo"'

However, I have two questions regarding this:

1. is the order of the array processing guaranteed to be the same?

In other words: are @.id and @.type guaranteed to always refer to the keys of
the same element in the array?

2. is this less efficient that the jsonb_path_exists?

The jsonpath for the @@ operator contains two "iterator expression", and I wonder
if Postgres is smart enough to only iterate over the array once.

Thanks
Thomas

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2021-03-09 13:20:04 Re: Question about when PostgreSQL 11.0 was released
Previous Message Thomas Kellerer 2021-03-09 10:19:22 Re: Log files polluted with permission denied error messages after every 10 seconds