Support CREATE TYPE FROM 'JSON SCHEMA'

[rjnn]

Filed for later, but relationalizing JSON feeds is manual and laborious. For instance, the recent changes wikimedia JSON feed ( https://stream.wikimedia.org/v2/stream/recentchange) has a JSONSchema field, which we could use to autopopulate columns. Without that, I had to slowly construct this view definition by hand:

select 
(val->'$schema')::text as schema, 
(val->'bot')::bool as bot, 
(val->'comment')::text as comment, 
(val->'id')::float::int as id,
(val->'length'->'new')::float::int as length_new,
(val->'length'->'old')::float::int as length_old,
val->'meta' as meta,
(val->'minor')::bool as minor,
val->'namespace' as namespace,
val->'parsedcomment' as parsedcomment,
(val->'revision'->'new')::float::int as revision_new,
(val->'revision'->'old')::float::int as revision_old,
(val->'server_name')::text as server_name,
(val->'server_script_path')::text as server_script_path,
(val->'server_url')::text as server_url,
(val->'timestamp')::float as timestamp,
(val->'title')::text as title,
(val->'type')::text as type,
(val->'user')::text as user,
(val->'wiki')::text as wiki
from v;

Which isn't the end of the world, but JSON schema would alleviate this.

Filed for later.

[rjnn]

Closing in favor of #2679.

[benesch]

You didn't actually close this. :D

[rjnn]

Reopening! From https://github.com/MaterializeInc/materialize/issues/2679#issuecomment-614922357 by @benesch, the product ask is pretty clear. I've copy-and-pasted his comment here with some tweaks, so the following is straight up plagiarism:

We want support for statically determining types from a given JSON schema:

CREATE TYPE x (a int, b int);
SELECT * FROM json_populate_record(null::x, '{"a":1,"b":2}');

CREATE TYPE x FROM JSON SCHEMA '...';
SELECT * FROM json_populate_record(NULL::x, ...);

[benesch]

Crosslinking that #3516 is a precursor to this.