sql - 使用自连接在 row_to_json() 中复制 JSON 元素

Question

这是这个优秀问答的后续:13227142 .

我几乎必须做同样的事情(受 PostgreSQL 9.2 的限制)但我只使用一个表。因此，查询使用自连接(为了生成正确的 JSON 格式)，这会导致重复的 id 字段。我怎样才能避免这种情况？

例子:

CREATE TABLE books
(
  id serial primary key,
  isbn text,
  author text,
  title text,
  edition text,
  teaser text
);

SELECT row_to_json(row)
FROM
(
  SELECT id AS bookid,
  author,
  cover
  FROM books
  INNER JOIN 
  (
    SELECT id, title, edition, teaser
    FROM books
  ) cover(id, title, edition, teaser)
  USING (id)
) row;

结果:

{
  "bookid": 1,
  "author": "Bjarne Stroustrup",
  "cover": {
    "id": 1,
    "title": "Design and Evolution of C++",
    "edition": "1st edition",
    "teaser": "This book focuses on the principles, processes and decisions made during the development of the C++ programming language"
  }
}

我想去掉“cover”中的“id”。

Answer 1

事实证明这是一项棘手的任务。据我所知，用一个简单的查询是不可能实现的。一种解决方案是使用预定义的数据类型:

CREATE TYPE bookcovertype AS (title text, edition text, teaser text);

SELECT row_to_json(row)
FROM
(
  SELECT books.id AS bookid, books.author, 
    row_to_json(row(books.title, books.edition, books.teaser)::bookcovertype) as cover
  FROM books
) row;