在psql中获取一条记录的数据数组

Question

我有两张表，分别是人员和账户。

此人有身份证和姓名。

+----------+--------+--------------------------------------------+
| Column   | Type   | Modifiers                                  |
|----------+--------+--------------------------------------------|
| id       | bigint |  not null generated by default as identity |
| name     | text   |                                            |

和帐户有id，名称，id

+----------+-----------+--------------------------------------------+
| Column   | Type      | Modifiers                                  |
|----------+-----------+--------------------------------------------|
| id       | bigint    |  not null generated by default as identity |
| name     | text      |                                            |
| ids      | integer[] |                                            |
+----------+-----------+--------------------------------------------+
Indexes:
"account_pkey" PRIMARY KEY, btree (id)
Check constraints:
"account_ids_check" CHECK (array_length(ids, 0) < 4)

• 我正在id中存储人员的id。我有两个问题：

1. account表中的id可以是外键数组，并指向人员的id？如果是，怎么做？

2. 我想得到id和帐户的名称以及id中id的人的名称和id。像这样。

   id：名称：ids:[｛id:，name：｝，｛id：，name：}，｛id：，name:｝]

我正在使用此查询，但它会给我错误

SELECT aa.id,
aa.name,
array(SELECT json_build_object ( 'id', p.id,'name', p.name  from  person p JOIN  account a ON  p.id = ANY(a.ids) ) ) as pins
from  account aa where aa.id = 1

Answer 1

演示

我重构了你的代码。希望它能解决你的问题。基本上，我的代码理念是一个人不能拥有超过4个帐户。一个帐户属于多人似乎不是那么直观的我。

---

json=>https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg和https://dba.stackexchange.com/questions/90858/postgres-multiple-columns-to-json

---

begin;
create table person
(person_id bigserial primary key,
name text not null,
account_ct integer default 0,  constraint account_ct_max4 check( account_ct BETWEEN 0 AND 4 ));
create table account(
account_id serial primary key,
account_type text,
account_limits numeric,
person_id bigint,
constraint person_ref_fkey foreign key (person_id)
references person(person_id) match simple
on update cascade on delete cascade
);
commit;

想法是一个人可以有多个账户，但不超过4个。我们使用触发器来强制执行。

--tojson one person's account info.
select p.person_id,
p.name,
json_agg( 
(select x from (select a.account_id, a.account_type) as x) ) as item
from account a join person p on a.person_id = p.person_id
where p.person_id = 1
group by 1,2;

--tojson one person's person info. account info not convert to json. 
select account_id,
account_type,
json_build_object('id',p.person_id,'name',p.name)
from account a, person p
where p.person_id = 1;