CODEWITHSUNDEEP
postgresqlsubquery
augustomen
augustomen

Reputation: 9759

Subquery as a JSON field

Using the hypothetical schema:

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');

Could I obtain a list of objects (one per row), and a JSON field that represents object's properties?

I know I can use the ARRAY() function with a subquery to retrieve an array of values, for example:

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 | {}                           |
+----+----------+------------------------------+

But could I make a query that instead of an ARRAY, it would return me a JSON column with the subquery in it? My goal is to obtain for example:

+---+----------+----------------------------------------------------------------------------------------+
| 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 | []                                                                                     |
+---+----------+----------------------------------------------------------------------------------------+

Upvotes: 8

Views: 6293

Answers (1)

User9123
User9123

Reputation: 1733

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

Upvotes: 7

Related Questions