Pg_jsonschema – JSON Schema Support for Postgres (opens in new tab)

(supabase.com)

219 pointsawalias3y ago44 comments

44 comments

33 comments · 14 top-level

onderkalaci3y ago· 6 in thread

PG community had a similar patch, which got reverted from PG 15 on the last minute: https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-...

kiwicopple3y ago

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.

mkurz3y ago

Is there a chance it gets committed during the current commitfest? See https://commitfest.postgresql.org/42/4086/

This years feature freeze will be on April 8th (https://www.postgresql.org/message-id/flat/9fbe60ec-fd1b-6ee...), so if it does not get committed within the next two and a half weeks it will miss this years Postgres release in September...

kiwicopple3y ago

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.

1 more reply

nextaccountic3y ago

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.

debugdog3y ago

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.

nextaccountic3y ago

How can this be used to validate JSON? (and prevent invalid json from being inserted into the database in the first place). I don't see "jsonschema" mentioned in that post.

Perhaps Postgres could support jsonschema validation natively.

boomskats3y ago· 4 in thread

This is long overdue and elegantly done. Great work!

On a personal note, it's great to finally see Neon & Supabase playing with each other. Much more interesting to me than Hasura.

endisneigh3y ago

There’s nothing stopping you from using this with Hasura. Don’t see the relevance.

tough3y ago

The power of FOSS

thangngoc893y ago

Hasura is also FOSS

zwily3y ago

Hasura is open core. Given the massive price increase they just did for their hosted version, I’d expect more and more future features will not land in core at all, to push people to pay.

3 more replies

korijn3y ago· 4 in thread

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

dtech3y ago

You could add a schema_version column and use one constraint per schema version. It will only be per major version, if the schema is backwards compatible there's no problem.

willmeyers3y ago

Add a new constraint that supports the new schema version, migrate json columns to conform to new schema as need be, and then drop old constraint. This assumes your schema is backwards compatible. If it isn't I would probably not use a DB constraint and just handle everything on the application layer.

alexvoda3y ago

If the schema is not backwards compatible it either means you will have to do a data migration or it means you want to keep track of the schema version along with the data. In both cases it is much easier if the json columns are split off into a separate table.

korijn3y ago

Thanks, that makes sense.

Pxtl3y ago· 2 in thread

I've tried to use JSON Schema and the big gap I couldn't figure was how to handle polymorphism.

That is, if I have a case where object

    {
        "Foo": {
            "Type":"Fred"
            "Bar":1
            "Baz":2
            "Quux":2
        }
    }

and the object

    {
        "Foo": {
            "Type":"Waldo"
            "Bar":1
            "Corge":2
            "Xyzzy":7
        }
    }

are both valid, without just allowing any object members or allowing the union of their members.

I did a hack by multiplexing the types into a child-object, but that was ugly and clumsy.

In XSD or any statically-typed programming language I could handle this trivially using types and polymorphism, because "Fred" and "Waldo" would be different types.

But I can't figure out how to do that in Json Schema.

mullsork3y ago

IIRC that's what oneOf is for. i.e. a discriminated union / sum type. My experience with oneOf is that tooling support for it is terrible.

Pxtl3y ago

Ah, I didn't realize you could use oneOf with refs or objects, I've only ever seen it used with primitives. Thanks!

Kydlaw3y ago· 1 in thread

> 2022-08-19

Has anyone tried it? Any feedback on it?

wendyshu3y ago

It works just fine but it will only give a boolean value valid/invalid, it won't tell you why it's invalid.

naedish3y ago· 1 in thread

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

kkirsche3y ago

With pydantic’s funding, it wouldn’t surprise me if they enter this space themselves (the company side not the existing python library side)

Funding Ref: https://pydantic.dev/announcement/

rapfaria3y ago· 1 in thread

Wouldn't you wanna save the json even if the structure is incorrect?

rco87863y ago

If you want that behavior, just don't use json schema.

julian373y ago

I've found that this PL/pgSQL implementation also works well when you don't need maximum performance:

https://github.com/gavinwahl/postgres-json-schema

mehdix3y ago

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

hhthrowaway12303y ago

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

anonu3y ago

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 "where" the issue is in your JSON object, most schema validators don't provide enough guidance, they just say "something is wrong" as it appears this one does.

marwis3y ago

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

raydiatian3y ago

This looks rad.

roenxi3y ago

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't want to use it to set up their schemas in pg proper.

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.

j / k navigate · click thread line to collapse

44 comments

33 comments · 14 top-level

onderkalaci3y ago· 6 in thread

PG community had a similar patch, which got reverted from PG 15 on the last minute: https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-...

kiwicopple3y ago

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.

mkurz3y ago

Is there a chance it gets committed during the current commitfest? See https://commitfest.postgresql.org/42/4086/

kiwicopple3y ago

1 more reply

nextaccountic3y ago

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?

debugdog3y ago

nextaccountic3y ago

How can this be used to validate JSON? (and prevent invalid json from being inserted into the database in the first place). I don't see "jsonschema" mentioned in that post.

Perhaps Postgres could support jsonschema validation natively.

boomskats3y ago· 4 in thread

This is long overdue and elegantly done. Great work!

On a personal note, it's great to finally see Neon & Supabase playing with each other. Much more interesting to me than Hasura.

endisneigh3y ago

There’s nothing stopping you from using this with Hasura. Don’t see the relevance.

tough3y ago

The power of FOSS

thangngoc893y ago

Hasura is also FOSS

zwily3y ago

Hasura is open core. Given the massive price increase they just did for their hosted version, I’d expect more and more future features will not land in core at all, to push people to pay.

3 more replies

korijn3y ago· 4 in thread

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

dtech3y ago

You could add a schema_version column and use one constraint per schema version. It will only be per major version, if the schema is backwards compatible there's no problem.

willmeyers3y ago

alexvoda3y ago

korijn3y ago

Thanks, that makes sense.

Pxtl3y ago· 2 in thread

I've tried to use JSON Schema and the big gap I couldn't figure was how to handle polymorphism.

That is, if I have a case where object

    {
        "Foo": {
            "Type":"Fred"
            "Bar":1
            "Baz":2
            "Quux":2
        }
    }

and the object

    {
        "Foo": {
            "Type":"Waldo"
            "Bar":1
            "Corge":2
            "Xyzzy":7
        }
    }

are both valid, without just allowing any object members or allowing the union of their members.

I did a hack by multiplexing the types into a child-object, but that was ugly and clumsy.

In XSD or any statically-typed programming language I could handle this trivially using types and polymorphism, because "Fred" and "Waldo" would be different types.

But I can't figure out how to do that in Json Schema.

mullsork3y ago

IIRC that's what oneOf is for. i.e. a discriminated union / sum type. My experience with oneOf is that tooling support for it is terrible.

Pxtl3y ago

Ah, I didn't realize you could use oneOf with refs or objects, I've only ever seen it used with primitives. Thanks!

Kydlaw3y ago· 1 in thread

> 2022-08-19

Has anyone tried it? Any feedback on it?

wendyshu3y ago

It works just fine but it will only give a boolean value valid/invalid, it won't tell you why it's invalid.

naedish3y ago· 1 in thread

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

kkirsche3y ago

With pydantic’s funding, it wouldn’t surprise me if they enter this space themselves (the company side not the existing python library side)

Funding Ref: https://pydantic.dev/announcement/

rapfaria3y ago· 1 in thread

Wouldn't you wanna save the json even if the structure is incorrect?

rco87863y ago

If you want that behavior, just don't use json schema.

julian373y ago

I've found that this PL/pgSQL implementation also works well when you don't need maximum performance:

https://github.com/gavinwahl/postgres-json-schema

mehdix3y ago

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

hhthrowaway12303y ago

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

anonu3y ago

marwis3y ago

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

raydiatian3y ago

This looks rad.

roenxi3y ago

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.

j / k navigate · click thread line to collapse