postgresql - 子查询作为 JSON 字段

Question

使用假设模式:

CREATE TABLE obj (id INT, name VARCHAR);
CREATE TABLE objprop (obj_id INT, key VARCHAR, value VARCHAR);
INSERT INTO obj VALUES
    (1, 'Object 1'),
    (2, 'Object 2'),
    (3, 'Object 3');
INSERT INTO objprop VALUES
    (1, 'created', '2020-02-16'),
    (1, 'updated', '2020-02-28'),
    (2, 'created', '2020-02-01');

我能否获得一个对象列表(每行一个)和一个表示对象属性的 JSON 字段？

我知道我可以使用 ARRAY()带有子查询的函数来检索值数组，例如:

SELECT id, name, ARRAY(SELECT value FROM objprop where obj_id=id) values FROM obj;

+----+----------+------------------------------+
| id | name     | values                       |
+----+----------+------------------------------+
| 1  | Object 1 | {'2020-02-16', '2020-02-28'} |
| 2  | Object 2 | {'2020-02-01'}               |
| 3  | Object 3 | {}                           |
+----+----------+------------------------------+

但是我可以做一个查询而不是 ARRAY ，它会给我一个 JSON包含子查询的列？我的目标是获得例如:

+---+----------+----------------------------------------------------------------------------------------+
| 1 | Object 1 | [{"key": "created", "value": "2020-02-16"}, {"key": "updated", "value": "2020-02-28"}] |
| 2 | Object 2 | [{"key": "created", "value": "2020-02-01"}]                                            |
| 3 | Object 3 | []                                                                                     |
+---+----------+----------------------------------------------------------------------------------------+

Answer 1

SELECT 
    id, 
    name, 
    COALESCE((
       SELECT json_agg(json_build_object('key', key, 'value', value))
       FROM objprop where obj_id=id
    ), '[]'::json) vals 
FROM 
    obj;