postgres-json-schema VS pg_jsonschema

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

This is awesome -- really excited that Supabase is picking this up with their commitment to open source in general and PG in particular.

Some prior art:

- https://github.com/gavinwahl/postgres-json-schema (mentioned in the repo)

# Based on debian-slim FROM postgres:12 RUN mkdir -p /app RUN apt-get update # pgroonga extension # Does not officially support alpine RUN apt-get install -y wget RUN wget https://packages.groonga.org/debian/groonga-apt-source-latest-buster.deb RUN apt install -y -V ./groonga-apt-source-latest-buster.deb RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - RUN apt-get update RUN apt-get install -y -V postgresql-12-pgdg-pgroonga RUN apt-get install -y -V groonga-tokenizer-mecab RUN apt-get install -y -V groonga-token-filter-stem RUN apt-get install -y git make # postgres-json-schema extension WORKDIR /app RUN git clone --depth 1 https://github.com/gavinwahl/postgres-json-schema.git RUN cd postgres-json-schema && make install # groonga-tokenizer-friso extension # WORKDIR /app # RUN apt-get install -y build-essential automake libtool libgroonga-dev # RUN git clone --depth 1 https://github.com/kenhys/groonga-tokenizer-friso.git # RUN wget -c https://github.com/lionsoul2014/friso/archive/v1.6.4-release.tar.gz -O - | tar -xz # RUN cd friso-1.6.4-release/src && make && make install # RUN cd groonga-tokenizer-friso && ./autogen.sh && ./configure --with-friso=/usr --with-friso-ini=../friso-1.6.4-release/friso.ini && make && make install # pg_jieba extension WORKDIR /app RUN apt-get install -y cmake build-essential postgresql-server-dev-12 RUN git clone --depth 1 https://github.com/jaiminpan/pg_jieba.git WORKDIR /app/pg_jieba RUN git submodule update --init --recursive RUN mkdir build && cd build && cmake -DPostgreSQL_TYPE_INCLUDE_DIR=/usr/include/postgresql/12/server .. && make && make install

And lots of interesting extensions use it, like

https://github.com/tembo-io/pgmq

https://github.com/zombodb/zombodb

https://github.com/supabase/pg_jsonschema

> In other words, there is still a (lossy) translation layer, it just happens to be in the RDBMS rather than in-app.

It's not lossy if your application can guarantee a json <-> datatype roundtrip and the json is validated with jsonschema (generated by your application)

In Rust it's something like this

https://serde.rs/ to do the data type <-> json mapping

https://docs.rs/schemars/latest/schemars/ to generate jsonschema from your types

https://github.com/supabase/pg_jsonschema to validate jsonschema in your database (postgres). with this setup it's interesting (but not required) to also use https://docs.rs/jsonschema/latest/jsonschema/ to validate the schema in your application

Pretty exciting!

What about optionally validating some columns with jsonschema? Perhaps using https://github.com/supabase/pg_jsonschema - is using other postgres extensions supported in FerretDB? (if not, maybe it's feasible to incorporate the code of pg_jsonschema in FerretDB?)

I'm not currently using it, but I'm strongly considering validating json in postgres with https://github.com/supabase/pg_jsonschema - which uses the https://docs.rs/jsonschema/latest/jsonschema/ Rust crate

So I'm not sure if my feedback is valid but, I sure hope that the jsonschema crate follows the spec! Otherwise I'll never use jsonschema but instead something-not-exactly-jsonschema. In other words.. you better not break anything.

Sounds dumb, but I want JSON field schema validation. I added a JSON column for flexible data, and although I'm happy with its flexibility, I kinda hope I can validate the JSON data structure. Recently I just found an extension [1] and will try soon.

[1] https://github.com/supabase/pg_jsonschema

https://github.com/supabase/pg_jsonschema is new and looks good

Born as an excuse to play with pgx, pg_jsonschema is a solution we're exploring to allow enforcing more structure on json and jsonb typed postgres columns. Only 10 lines of code 😎