Pg_jsonschema – JSON Schema Support for Postgres

PG community had a similar patch, which got reverted from PG 15 on the last minute: <a href="https:&#x2F;&#x2F;www.depesz.com&#x2F;2022&#x2F;04&#x2F;06&#x2F;waiting-for-postgresql-15-json_table&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.depesz.com&#x2F;2022&#x2F;04&#x2F;06&#x2F;waiting-for-postgresql-15-...</a>

I&#x27;ve found that this PL&#x2F;pgSQL implementation also works well when you don&#x27;t need maximum performance:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;gavinwahl&#x2F;postgres-json-schema">https:&#x2F;&#x2F;github.com&#x2F;gavinwahl&#x2F;postgres-json-schema</a>

This is long overdue and elegantly done. Great work!<p>On a personal note, it&#x27;s great to finally see Neon &amp; Supabase playing with each other. Much more interesting to me than Hasura.

I&#x27;ve tried to use JSON Schema and the big gap I couldn&#x27;t figure was how to handle polymorphism.<p>That is, if I have a case where object<p><pre><code> { &quot;Foo&quot;: { &quot;Type&quot;:&quot;Fred&quot; &quot;Bar&quot;:1 &quot;Baz&quot;:2 &quot;Quux&quot;:2 } } </code></pre> and the object<p><pre><code> { &quot;Foo&quot;: { &quot;Type&quot;:&quot;Waldo&quot; &quot;Bar&quot;:1 &quot;Corge&quot;:2 &quot;Xyzzy&quot;:7 } } </code></pre> are both valid, without just allowing any object members or allowing the union of their members.<p>I did a hack by multiplexing the types into a child-object, but that was ugly and clumsy.<p>In XSD or any statically-typed programming language I could handle this trivially using types and polymorphism, because &quot;Fred&quot; and &quot;Waldo&quot; would be different types.<p>But I can&#x27;t figure out how to do that in Json Schema.

The article is also a joy to read. Well done.

Ah yeah super nice always wanted schema support in progress love to see them hammering away on top of the solid foundation of postgres

&gt; 2022-08-19<p>Has anyone tried it? Any feedback on it?

So how do you apply this in the real world when dealing with schema versions and database migrations?

well written article and looks like a great extension. However, my only issue with JSON Schema is that it becomes unsupportable once your JSON objects get too big: to many keys or too many items in an array for example. If you are looking to find &quot;where&quot; the issue is in your JSON object, most schema validators don&#x27;t provide enough guidance, they just say &quot;something is wrong&quot; as it appears this one does.

The detail error from example is pretty awful and missing any useful detail. Is it the limitation of the jsonschema library?

This is interesting. Would be curious to see if this can replace pydantic for specific cases.

Wouldn&#x27;t you wanna save the json even if the structure is incorrect?

This looks rad.

I can see some technical advantages to supporting JSON schema directly; but I suspect most people will be using this extension because SQL is really ugly and they don&#x27;t want to use it to set up their schemas in pg proper.<p>It says a lot about how weak the SQL syntax is. An extension to replace CREATE TABLE with a JSON schema construct would be wildly popular.