Postgresql - Use selected column from one table as json select statements

Question

https://www.db-fiddle.com/f/gZXz9hJRzpiEmDr7V8PXEG/0

Postgresql 10.x

Consider the following tables:

CREATE TABLE attributes (
    attr TEXT
);

INSERT INTO attributes VALUES('sth1');
INSERT INTO attributes VALUES('sth2');

CREATE TABLE items (
    name TEXT,
    custom JSONB
);

INSERT INTO items VALUES ('A', '{"sth1": "Hello"}');
INSERT INTO items VALUES ('B', '{"sth1": "Hello", "sth2": "Okay"}');
INSERT INTO items VALUES ('C', '{"sthNOT": "Hello", "sth2": "Okay"}');

My goal is to only query the columns from the attributes table as Json keys in the ìtems.custom column - so the query always returns the same set of keys.

When I know the columns I would do just:

SELECT name, custom->>'sth1', custom->>'sth2' FROM items;

I would like to make this query "dynamic" - so there can be arbitrary key definition in the attributes table.

It would also be acceptable for me, to create a new Json object in the query - only containing the keys defined in the attributes table and the corresponding values from the items.custom column. So merging one Json object created from the attributes with the items.custom data would be an option.

Is there a way to accomplish this in Postgres?

score 2 · Accepted Answer · answered Jul 11 '18 at 18:53

You need a function to dynamically format and execute a suitable query. The function returns rows with name and a jsonb object data:

create or replace function select_from_items()
returns table(name text, data jsonb) language plpgsql as $$
declare
    cols text;
begin
    select string_agg(format('%1$L, custom->>%1$L', attr), ', ')
    from attributes
    into cols;

    return query execute format('
        select name, jsonb_strip_nulls(jsonb_build_object(%s)) 
        from items', cols);
end $$;

select * from select_from_items();

 name |               data                
------+-----------------------------------
 A    | {"sth1": "Hello"}
 B    | {"sth1": "Hello", "sth2": "Okay"}
 C    | {"sth2": "Okay"}
(3 rows)

It is therefore your second option. The first one assumes creating a kind of pivot table and needs more complicated techniques, see e.g.

Thanks for detailed answer! Very much appreciated. – madflow Jul 12 '18 at 07:30 — madflow, Jul 12 '18 at 07:30

Postgresql - Use selected column from one table as json select statements

1 Answers1