Scenario: Use JSON-schema provided by FHIR for validation of JSONB field

[terminalkitten]

I've worked out some kind of idea today, maybe we can explore this further:

https://github.com/terminalkitten/fhirebase-core-json-schema

[niquola]

👍
Sounds interesting.
Do you know postgres extensions or c/c++ libraries for json-schema?
We also can generate jsquery constraint instead of json-schema.

[niquola]

Potentially we can run it using plv8 and js engines like ajv

[terminalkitten]

Do you know postgres extensions or c/c++ libraries for json-schema?

Nope, but I will look further into the matter.
I've never heard of jsquery constraints ... have to check that one out.

Potentially we can run it using plv8 and js engines like ajv

Yes, certainly

I also thought about setting up tables and then transforming the JSON to match the table. This way the table can maintain proper types and constraints.

Only this means a lot is happening underneath which could hurt performance. On the other-hand, fulll-text search and more will be available. I'll try to make a POC.

[terminalkitten]

Here is more information on populating record / table from JSON:

• https://www.postgresql.org/docs/current/static/functions-json.html
• https://stackoverflow.com/questions/17905501/postgresql-insert-data-into-table-from-json

From docs:

PGSQL function :

json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]

Insert single object:

insert into patient(name) select name from json_populate_record(NULL::test, '{"name": "John"}');

Bulk update multiple records:

insert into example(a,b) select (a,b) from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]');

[niquola]

Here is json schema extension - https://github.com/gavinwahl/postgres-json-schema

[niquola]

@terminalkitten are you going to 19th conectathon?

[niquola]

Ups - this is a toy extension :(

[terminalkitten]

are you going to 19th conectathon?

@niquola nope, not possible for me right now goto Boston ;)
I'm looking at attending the Amsterdam DevDays on 14-16 nov ( i'm Dutch, so that would be easier )

[terminalkitten]

Here is json schema extension - https://github.com/gavinwahl/postgres-json-schema
Ups - this is a toy extension :(

I've used this extension in my fiddle.
To me it looks quite ok ... why do you think it's a toy extension?