CODEWITHSUNDEEP
jsonpostgresqlaggregate-functionsjsonb
Dmitry
Dmitry

Reputation: 867

Select data as JSONB where value used as json key

100500 times I found answer here but not now.

I have PostgreSQL 11.1 and table

CREATE TABLE public.bots_script_elements (
    id integer,
    icon text,
    CONSTRAINT bots_script_elements_pri PRIMARY KEY (id)
);

Values

ID  ICON
1   "begin"
2   "form"
3   "calendar"

How can I select data as json below?

{
  "1": {"id":1, "icon":"begin"},
  "2": {"id":2, "icon":"form"},
  "3": {"id":3, "icon":"calendar"}
}

Json object keys 1, 2 and 3 is value from ID column.

Upvotes: 1

Views: 96

Answers (1)

klin
klin

Reputation: 121554

Use the aggregate function jsonb_object_agg():

select jsonb_object_agg(id, to_jsonb(b))
from bots_script_elements b

Test it in rextester.

Upvotes: 1

Related Questions