Create jsonb data structure from columns
You can create a jsonb object by casting a string version of the object to jsonb:
> select '{"a": 1, "b": 2}'::jsonb;
jsonb
------------------
{"a": 1, "b": 2}
When using values from columns to create a jsonb object its not practical to construct a string. In this case you want to use jsonb_build_object
:
> select jsonb_build_object('a', a.x) from (select x from generate_series(1, 10) as x) as a;
jsonb_build_object
--------------------
{"a": 1}
{"a": 2}
{"a": 3}
{"a": 4}
{"a": 5}
{"a": 6}
{"a": 7}
{"a": 8}
{"a": 9}
{"a": 10}
jsonb_build_object
is a variatic function and takes a variable number of arguments. So you can add additional values like this:
jsonb_build_object('a', 1, 'b', 2)
Tweet