feat(expr): to_jsonb

[xiangjinwu]

https://www.postgresql.org/docs/16/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE

Converts any SQL value to jsonb.

• Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON).
• Otherwise, if there is a cast from the SQL data type to jsonb, the cast function will be used to perform the conversion;
• otherwise, a scalar JSON value is produced.
  • For any scalar other than a number, a Boolean, or a null value,
  • the text representation will be used, with escaping as necessary to make it a valid JSON string value.

to_json('Fred said "Hi."'::text) → "Fred said \"Hi.\""
to_jsonb(row(42, 'Fred said "Hi."'::text)) → {"f1": 42, "f2": "Fred said \"Hi.\""}

Note:

• This should be the basis of existing jsonb_agg and jsonb_object_agg, which currently relies on Into<serde_json::Value>.
• None of our data type is allowed to cast to jsonb. The crossed PostgreSQL rule is for hstore type.
• Strictly speaking, to_jsonb(1::bigint << 55) is better encoded as json string rather than json number (rfc or proto3). But we can keep it PostgreSQL-compatible.
• Strictly speaking, nan and inf are not allowed. But PostgreSQL encodes them as string, and refuses to decode them back:

test=# with t(v) as (values (1.5::real), ('nan')) select (jsonb_agg(v) -> generate_series(0, 0))::real from t;
 float4 
--------
    1.5
(1 row)

test=# with t(v) as (values (1.5::real), ('nan')) select (jsonb_agg(v) -> generate_series(0, 1))::real from t;
ERROR:  cannot cast jsonb string to type real

• Strings are escaped, but jsonb is not double encoded:

test=# select to_jsonb('{"a":1}'::varchar);
  to_jsonb   
-------------
 "{\"a\":1}"
(1 row)

test=# select to_jsonb('{"a":1}'::jsonb);
 to_jsonb 
----------
 {"a": 1}
(1 row)

test=# select to_jsonb(array['{"a":1}']);
   to_jsonb    
---------------
 ["{\"a\":1}"]
(1 row)

test=# select to_jsonb(array['{"a":1}'::jsonb]);
  to_jsonb  
------------
 [{"a": 1}]
(1 row)

• reference

[KeXiangWang]

Finished in #13161.

Here' the summary of converting rules:

• bool
  • converts to jsonb bool trivially
• int16 / int32
  • converts to jsonb number trivially
• float32 / float64
  • converts to jsonb number except for nan / inf / -inf
• int64 / decimal
  • may lose precision as IEEE 754 double, but PostgreSQL uses number (backed by decimal)
• int256
  • converts to jsonb string using ToText. Since int256 is usually used for blockchain addresses, which use all the 256-bits, we convert it to string directly.
• varchar
  • converts to jsonb string trivially
• interval / date / time / bytea
  • converts to jsonb string using ToText
• timestamp
  • converts to jsonb string using a new format, with T as separator (to be aligned with PostgreSQL)
• timestamptz
  • converts to jsonb string using a new format, with T as separator. Currently session timezone is ignored.
• jsonb
  • to_owned
• list
  • convert recursively
• struct
  • convert recursively
• serial
  • currently jsonb string using ToText, but we provide no semantic guarantee on it and may change it freely in the future.

Check the PR for more details.

[KeXiangWang]

After #13198, int64 is now directly stored and printed as i64 to avoid the overhead of conversion to IEEE 754 double.