使用PostgREST的RestAPI操作之 类型转JSON对象 | 嵌入视图

将类型转换为自定义JSON对象

 

使用PostgREST时，您可能已经注意到，当您期望使用JSON对象或数组时，某些PostgreSQL类型会转换为JSON字符串。例如，让我们看看范围类型的情况。

-- example taken from https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPES-EXAMPLES
create table reservations (
  room   int
, during tsrange
);

insert into
   reservations
values
    (1108, tsrange('2010-01-01 14:30', '2010-01-01 15:30'));

在这里，我们有一个名为列中的tsrange类型，我们想通过PostgREST把它作为JSON。

curl "http://localhost:3000/reservations"

结果：

[
  {
   "room":1108,
   "during":"[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")"
  }
]

该期间的值可能不是你想要的格式。我们得到一个JSON字符串，因为默认情况下PostgreSQL通过使用其text表示形式将类型转换为JSON 。我们可以通过创建CAST将此表示形式更改为自定义JSON对象。

要做到这一点，首先我们将定义将进行转换的功能tsrange来json。

create or replace function tsrange_to_json(tsrange) returns json as $$
  select json_build_object(
    'lower', lower($1)
  , 'upper', upper($1)
  , 'lower_inc', lower_inc($1)
  , 'upper_inc', upper_inc($1)
  );
$$ language sql;

使用此功能，我们将创建CAST。

create cast (tsrange as json) with function tsrange_to_json(tsrange) as assignment;

然后我们将执行请求并转换列。

curl "http://localhost:3000/reservations?select=room,during::json"

现在的结果是：

[
  {
   "room":1108,
   "during":{
      "lower" : "2010-01-01T14:30:00",
      "upper" : "2010-01-01T15:30:00",
      "lower_inc" : true,
      "upper_inc" : false
   }
  }
]

您可以使用相同的想法为不同类型的用户创建自定义CAST。

注意

如果您不想为内置类型修改CAST，则可以选择 为自己的类型创建自定义类型，tsrange然后添加自己的CAST。

create type mytsrange as range (subtype = timestamp, subtype_diff = tsrange_subdiff);

-- define column types and casting function analoguously to the above example
-- ...

create cast (mytsrange as json) with function mytsrange_to_json(mytsrange) as assignment;

 

从另一个模式嵌入表

 

假设您在模式中有一个人表，public并且该模式是通过PostgREST的db-schema公开的。

create table public.people(
  id        int  primary key
, full_name text
);

而你要嵌入的人用表的细节表在另一个名为架构private。

create schema if not exists private;

-- For simplicity's sake the table is devoid of constraints/domains on email, phone, etc.
create table private.details(
  id         int  primary key references public.people
, email      text
, phone      text
, birthday   date
, occupation text
, company    text
);

-- other database objects in this schema
-- ...
-- ...

要解决此问题，您可以在架构中创建详细信息视图public。我们将其称为public_details。

create view public.public_details as
select
    id
  , occupation
  , company
from
   private.details;

由于PostgREST支持嵌入视图，因此可以使用public_details嵌入人员。

让我们插入一些数据进行测试：

insert into
   public.people
values
   (1, 'John Doe'), (2, 'Jane Doe');

insert into
   private.details
values
   (1, 'jhon@fake.com', '772-323-5433', '1990-02-01', 'Transportation attendant', 'Body Fate'),
   (2, 'jane@fake.com', '480-474-6571', '1980-04-21', 'Geotechnical engineer', 'Earthworks Garden Kare');

重要

确保PostgREST的模式缓存是最新的。请参阅架构重载。

现在，发出以下请求：

curl "http://localhost:3000/people?select=full_name,public_details(occupation,company)"

结果应为：

[
   {"full_name":"John Doe","public_details":[{"occupation":"Transportation attendant","company":"Body Fate"}]},
   {"full_name":"Jane Doe","public_details":[{"occupation":"Geotechnical engineer","company":"Earthworks Garden Kare"}]}
]