postgresql - 如何仅返回 to_jsonb 调用中的选定列？

Question

我有一个表，想只输出 json 中的id和time列。

该表是：

CREATE TABLE IF NOT EXISTS "Session" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "account_id" TEXT,
  "time" BIGINT NOT NULL DEFAULT 1000000 * extract(epoch FROM now())
);

此调用输出所有值：

WITH ret AS (
      INSERT INTO "Session" ("account_id")
        VALUES ('hello')
      RETURNING *
)
SELECT to_json(ret) FROM ret;

--  {"id":"516a3510-cff9-43fc-a178-363b5ea53f03","account_id":"hello","time":1613227250536862}

但我只想输出id和time列。当我接到这个电话时，列键更改为"row"

WITH ret AS (
      INSERT INTO "Session" ("account_id")
        VALUES ('hello')
      RETURNING (id, time)
)
SELECT to_json(ret) FROM ret;

-- {"row":{"f1":"c628c347-b273-4f72-9186-c14cd32a16f0","f2":1613227326917707}}

我如何只输出这个：

{"id":"516a3510-cff9-43fc-a178-363b5ea53f03", "time":1613227250536862}

标签： postgresql

Answer 1

尝试RETURNING像这样删除括号：

WITH ret AS (
    INSERT INTO "Session" ("account_id")
        VALUES ('hello')
        RETURNING id, time
)
SELECT to_json(ret) FROM ret;