Provide a way to modify the JSON response through an SQL function (hypermedia, json-ld, json api)

[soyuka]

Hi,

I want to come back over the Json-LD and hypermedia related stuff we already talked about in:

• Proposal for nested routes and JSON-LD #343
• where is the "hypermedia" or "hateoas" word ? #941
• Relation to JSON API #437

Following @ruslantalpa recommendations on the topic, I attempted to implement JSON-LD over Postgrest and it is indeed possible (not without some drawbacks).

First you need to rewrite some paths (using nginx, or Caddy):

@resource path_regexp resource ^/api/([a-z_]+)/([a-z0-9-]+)$
@resources path_regexp resources ^/api/([a-z_]+)$

rewrite @resource /api/{re.resource.1}?id=eq.{re.resource.2}&select=*,"%40id":iri,"%40context":context
rewrite @resources /api/{re.resources.1}?select=*,"%40id":iri,"%40context":context,"%40type":type

handle /api/* {
  uri strip_prefix /api
  reverse_proxy postgrest:3000
}

Then, you'd need to add custom functions for @id, @context, @type (this example works on an /artists route):

CREATE OR REPLACE FUNCTION iri(schema.artists) RETURNS text AS 
$$
  declare iri text;
  begin
	perform set_config('response.headers',
      '[{"Content-Type": "application/ld+json"}]', true);
      select '/artists/' || $1.id into iri;
	return(iri);
  end
$$ LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION context(schema.artists) RETURNS text AS $$
    SELECT 'https://schema.org';
$$ language sql;

CREATE OR REPLACE FUNCTION type(schema.artists) RETURNS text AS $$
    SELECT 'MusicGroup';
$$ language sql;

However, this adds lots of functions to maintain if you want to implement it on most of your tables. Especially if, continuing like this I wanted to add the Hydra Core Vocabulary spec.
Would you see a solution to modify JSON responses via an SQL function? This would improve such implementations a lot.

I was thinking of a procedure call, just like you added for pre-request but for responses pre-response. For example:

create or replace function jsonld(data JSON, table_name regclass) returns JSON as $$
declare
  accept text := current_setting('request.header.accept', true);
begin
  if accept similar to '%application/json+ld%' then
    perform set_config('response.headers',
      '[{"Content-Type": "application/ld+json"}]', true);
  end if;
  
  -- extend json somehow adding @id, @type etc.

end; $$ language plpgsql;

Opinions?

[wolfgangwalther]

We are discussing content negotiation and returning custom mimetypes with generic handlers here: #1582 (comment). You might need to read the whole thread and related issues for context.

This boils down to allow you to add generic handler for application/ld+json as an aggregate function, which can then be used at all endpoints. However, we're not close, yet, to implementing that. Might take a while.

[soyuka]

nice thoughts on that topic it'd indeed be a good solution! Will definitely follow the issues, thanks for the link!