select_specific_column_to_json_agg.sql

select_specific_column_to_json_agg.sql

/*
https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg/69658#69658

*/

BEGIN;
SET local search_path = '';
CREATE temp TABLE tbl_a (
    id bigint,
    name text
) ON COMMIT DROP;
CREATE temp TABLE tbl_b (
    item_id bigint,
    col text,
    col2 text,
    col3 text
) ON COMMIT DROP;
INSERT INTO tbl_a (id, name)
SELECT
    g,
    to_char(g, 'fm000') || '_' || (
        SELECT
            relnamespace::regnamespace || '.' || relname
        FROM
            pg_class
        WHERE
            relname = 'tbl_a')
FROM
    generate_series(1, 10) g;
INSERT INTO tbl_b (item_id, col, col2, col3)
SELECT
    g,
    g::text || '_col',
    g::text || '_col2',
    g::text || '_col3'
FROM
    generate_series(1, 5) g,
    generate_series(1, 2) s;
SELECT
    a.id,
    a.name,
    json_agg(b.col, b.col2, b.col3) AS item
FROM
    tbl_a a
    JOIN tbl_b b ON b.item_id = a.id
GROUP BY
    1,
    2;
    
--------------------------------------------------------------------------------
----------------------cast to registered type-----------------------------------
CREATE TEMP TABLE x (
    col text,
    col2 text,
    col3 text
);

SELECT
    a.id,
    a.name,
    json_agg((b.col, b.col2, b.col3)::x) AS item
FROM
    tbl_a a
    JOIN tbl_b b ON b.item_id = a.id
GROUP BY
    1,
    2;
    
--------------------------------------------------------------------------------
----------------use subselect to construct a derived table----------------------
SELECT
    a.id,
    a.name,
    json_agg((
        SELECT
            x
        FROM (
            SELECT
                b.col, b.col2, b.col3) AS x)) AS item
FROM
    tbl_a a
    JOIN tbl_b b ON b.item_id = a.id
GROUP BY
    1,
    2;
    
    
-------------------------------------------------------------------------------
------------------use json_build_object function.------------------------------
SELECT
    a.id,
    a.name,
    json_agg(json_build_object('col', b.col, 'col2', b.col2, 'col3', b.col3))
FROM
    tbl_a a
    JOIN tbl_b b ON b.item_id = a.id
GROUP BY
    1,
    2;
END;