Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE orders (
  id int PRIMARY KEY
, total numeric(10, 2) DEFAULT 0 NOT NULL
);

CREATE TABLE items (
  id bigserial PRIMARY KEY
, order_id int NOT NULL
, name varchar(128) NOT NULL
, url varchar(128) DEFAULT '' NOT NULL
, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

INSERT INTO orders (id, total)
VALUES (12345, 123.45);

INSERT INTO items (order_id, name, url)
VALUES (12345, 'blue shirt', 'someurl'), (12345, 'red shirt', 'someurl');

CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 2


SELECT jsonb_pretty(
          to_jsonb(o.*)  -- taking whole row
      || (SELECT jsonb_build_object('items', jsonb_agg(i))
          FROM  (
             SELECT name, url  -- picking columns
             FROM   items i
             WHERE  i.order_id = o.id
             ) i
         )
       )
FROM   orders o
WHERE  o.id = 12345;


jsonb_pretty


{
    "id": 12345,
    "items": [
        {
            "url": "someurl",
            "name": "blue shirt"
        },
        {
            "url": "someurl",
            "name": "red shirt"
        }
    ],
    "total": 123.45
}



SELECT 1


SELECT row_to_json(sub, true)
FROM  (
   SELECT o.*
       , (SELECT json_agg(i)
          FROM  (
             SELECT name, url  -- pick columns to report
             FROM   items i
             WHERE  i.order_id = o.id
             ) i
         ) AS items
   FROM   orders o
   WHERE  o.id = 12345
   ) sub;


row_to_json


{"id":12345,
 "total":123.45,
 "items":[{"name":"blue shirt","url":"someurl"}, 
 {"name":"red shirt","url":"someurl"}]}



SELECT 1