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?

klin · Accepted Answer · 2018-07-11 18:53:19Z

2

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.

answered Jul 11, 2018 at 18:53

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

madflow Over a year ago

Thanks for detailed answer! Very much appreciated.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related