json - PostgreSQL 将多行转换为 json_build_object 中的 json 数组

Question

有两个一对多关系的表:

CREATE TABLE user (
  id bigserial PRIMARY KEY,
  name varchar(255) NOT NULL UNIQUE,
)

CREATE TABLE user_pets (
  id bigserial PRIMARY KEY,
  user_id int8 FOREIGN KEY REFERENCES user(id),
  name varchar(255)
)

user.id 由DEFAULT生成。

另一个包含用户及其宠物的 json 的表:

CREATE TABLE user_json (
  user_id int8 FOREIGN KEY REFERENCES user(id),
  full_info json
)

我希望我的 json 看起来像这样:

{
  "id": 1,
  "name": "John",
  "pets": [
    {
      "id": 1,
      "user": 1,
      "name": "Kitty"
    },
    {
      "id": 2,
      "user": 1,
      "name": "Cat"
    }
  ]
}

我正在使用 json_build_object 来处理用户，但我不知道如何将多行转换为宠物数组:

json_build_object(
  'id', user.id,
  'name', user.name,
  'pets', --WHAT SHALL I PASS HERE?
)

Postgres 10

Answer 1

最后，这对我有用:

WITH all_pets AS (
  SELECT *
  FROM pets
  WHERE pets.user_id = 1
)
INSERT INTO user_json (full_info)
SELECT
json_build_object(
  'id', user.id,
  'name', user.name,
  'pets', (
    SELECT json_agg(all_pets)
    FROM pets
  )
)
FROM user
WHERE user.id = 1;