clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818513 fiddles created (9191 in the last week).

CREATE TABLE myt (id int, name text, addr text); INSERT INTO myt VALUES (1, 'n1', 'ad1'), (2, 'n2', 'ad2');
2 rows affected
 hidden batch(es)


-- Postgres 9.4+ SELECT id, json_build_object('name', name, 'addr', addr) AS data FROM myt;
id data
1 {"name" : "n1", "addr" : "ad1"}
2 {"name" : "n2", "addr" : "ad2"}
 hidden batch(es)


-- Postgres 9.3 or older -- OP's version (with missing alias fixed) select * from ( select id, ( select row_to_json(d) from ( select name, addr from myt d where d.id=s.id ) d ) as data from myt s ) x;
id data
1 {"name":"n1","addr":"ad1"}
2 {"name":"n2","addr":"ad2"}
 hidden batch(es)


-- Simpler 1 SELECT id , (SELECT row_to_json(d) FROM ( SELECT name, addr FROM myt WHERE id = s.id ) d ) AS data FROM myt s;
id data
1 {"name":"n1","addr":"ad1"}
2 {"name":"n2","addr":"ad2"}
 hidden batch(es)


-- Simpler 2 SELECT id , (SELECT row_to_json(d) FROM (SELECT name, addr) d ) AS data FROM myt;
id data
1 {"name":"n1","addr":"ad1"}
2 {"name":"n2","addr":"ad2"}
 hidden batch(es)


-- Simpler 3 SELECT id, row_to_json((SELECT d FROM (SELECT name, addr) d)) AS data FROM myt;
id data
1 {"name":"n1","addr":"ad1"}
2 {"name":"n2","addr":"ad2"}
 hidden batch(es)