How to use to_jsonb as row_to_jsonb? Where the details about "how much"?
You can just use to_jsonb()
instead of row_to_json()
, example:
with the_table(a, b, c) as (
select 1, 'alfa', '2016-01-01'::date
)
select to_jsonb(t), row_to_json(t)
from the_table t;
to_jsonb | row_to_json
------------------------------------------+-------------------------------------
{"a": 1, "b": "alfa", "c": "2016-01-01"} | {"a":1,"b":"alfa","c":"2016-01-01"}
(1 row)
The first has a wider application than the other because of the type of arguments (anyelement
versus record
). For example, you can convert a Postgres array to json array using to_jsonb()
, that cannot be done with row_to_json()
:
select to_jsonb(array['a', 'b', 'c']);
to_jsonb
-----------------
["a", "b", "c"]
(1 row)
In case of the use of two arguments in row_to_json()
you should additionally use jsonb_pretty()
:
with the_table(a, b, c) as (
select 1, 'alfa', '2016-01-01'::date
)
select jsonb_pretty(to_jsonb(t)), row_to_json(t, true)
from the_table t;
jsonb_pretty | row_to_json
-----------------------+--------------------
{ +| {"a":1, +
"a": 1, +| "b":"alfa", +
"b": "alfa", +| "c":"2016-01-01"}
"c": "2016-01-01"+|
} |
(1 row)
you can cast json to jsonb row_to_json(...)::jsonb, not ideal but often does the trick
You can use to_jsonb
as a drop-in replacement for row_to_json
.
SELECT to_jsonb(rows) FROM (SELECT * FROM table) rows;