'How to use to_jsonb as row_to_jsonb? Where the details about "how much"?

I was testing some queries at pg9.4 in "JSON mode", and now I am checking if pg9.5 will bring all same JSONB functionality... But there are no row_to_jsonb() function (!). (why it is not orthogonal instruction set in the basic parameters?)

The guide only says "the to_jsonb function supplies much the same functionality". Where we can check "how much"? There are other specific JSONB guide about this details?

((Year 2022 update and pg upgrade))

The phrase "supplies much the same functionality" was removed on the version 13. The current Guide does not use the phrase neither the word "much".

Now row_to_json is an alias for to_json except when the optional boolean parameter is true — the result will be the inclusion of line feeds like in jsonb_pretty().

Now the functions to_jsonb and to_json are orthogonal (!), and typical use is the same:

SELECT t.a, t.b, to_jsonb(r) json_info
            -- or to_json(r)
FROM t, LATERAL (SELECT t.c,t.d,t.f) r;

-- or SELECT to_jsonb(r) FROM (SELECT c,d,f FROM t) r;
jsonpostgresqljsonbpostgresql-9.5


Solution 1:[1]

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)

Solution 2:[2]

You can use to_jsonb as a drop-in replacement for row_to_json.

SELECT to_jsonb(rows) FROM (SELECT * FROM table) rows;

Solution 3:[3]

you can cast json to jsonb row_to_json(...)::jsonb, not ideal but often does the trick

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 dbaston
Solution 3 Adam Popkiewicz

Related Questions