Hi!
During my work on bringing jsonpath patchset to commit, I was always
keeping in mind that we need to make jsonb_path_*() functions
immutable. Having these functions immutable, users can build
expression indexes over them. Naturally, in majority of cases one
doesn't need to index whole json documents, but only some parts of
them. jsonpath provide great facilities to extract indexable parts of
document, much more powerful than our current operator set.
However, we've spotted some deviations between standard and our implementation.
* like_regex predicate uses our regular expression engine, which
deviates from standard.
* We always do numeric computations using numeric datatype. Even if
user explicitly calls .double() method. Probably, our current
implementation still fits standard. But in future we may like to use
floating point computation in some cases for performance optimization.
These deviations don't look critical by itself. But immutable
functions make problematic fixing them in future. Also, I'm not sure
this is complete list of deviations we have. We might have, for
example, hidden deviations in handling strict/lax modes, which are
hard to detect and understand.
Therefore, I'm going to mark jsonb_path_*() functions stable, not
immutable. Nevertheless users will still have multiple options for
indexing:
1) jsonb_path_ops supports jsonpath matching operators in some cases.
2) One can wrap jsonb_path_*() in pl/* function and mark it as
immutable on his own risk. This approach is widely used to build
indexes over to_date()/to_timestamp().
3) We're going to provide support of jsonpath operators in jsquery
extension before release of PostgreSQL 12.
I'd like to note I don't mean we wouldn't ever have immutable
functions for jsonpath evaluation. I think once we sure enough that
we know immutable subset of jsonpath, we may define immutable
functions for its evaluation.
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
