this would be our preference. we'll try to support this for the next commitfest, and if it gets merged then we will deprecate our extension in favour of the native solution.
I'll flag it with the team this week. I'm not sure what the blocker was previously, but it might just be a matter of submitting the patch again (with minor changes) so that it's "in" for commitfest, with someone willing to own the work over the next few months.
I think the things that needed to be fixed from last year are already committed (more general stuff not directly related to the JSON patches).
Also according to this message at least partial stuff from the JSON patches should be committed "...in the next few days..." however that was two weeks ago: https://www.postgresql.org/message-id/454db29b-7d81-c97a-bc1...
I am a bit worried, even though the patches seem to be "stable", they will miss the deadline... (since I would need those features as well)
I don't know any of the people involved in this patch, so I've sent it to Alexander Korotkov to get his opinion. I'll let you know his response after he has a chance to look at it.
> This is very long story starting from 2017. This patch should finally be committed. Some preliminary infrastructure already landed to PostgreSQL 16. Regarding SQL/JSON itself I doubt it will be committed to PostgreSQL 16, because feature freeze is coming soon. It's likely be postponed to PostgreSQL 17.
> Regarding replacement for pg_jsonschema, I don't think it will be a good replacement. Yes, one can construct a jsonpath expression which checks if particular items have particular data types. But I doubt that is nearly as convenient as jsonschema.
It looks like there would still be some benefit for pg_jsonschema, unless the community decided that they wanted support jsonschema validation. We could propose this, but I don't think it would arrive to pg core any time soon.
That JSON_TABLE feature looks pretty useful [0], but it seems complimentary to pg_jsonschema. Can it actually be used to validate a json prior to inserting it to the database?
[0] For my use case, there is a problem: if the json represents a sum type (like Rust enums or Haskell ADTs) as SQL tables. Often you will have a "tag" that specifies which variant the data encodes, and each one has its own properties. When representing as a table, you will usually add the fields of all variants as columns, setting as non-null only the fields belonging to the variant of each row. And the reason I insert jsons into the database is really just to represent sum types in a better way.
Well written article! Also very glad to hear your approach to support the native implementation. For all of our projects when we're integrating external services we usually keep the relevant original JSON responses as a jsonb as kind of a backup. Next to that we extract the data we'll be using to queryable data. To be able to use those "dumps" directly would be a nice thing to have.
