Returning JSON or JSONB

Dane Foster <studdugie@gmail.com> wrote:

Hello,

I was wondering when returning JSON data from a PostgreSQL function for
consumption by clients (e.g., PHP or Lua) does it make any difference to
declare the function's return type as JSON or JSONB?

JSONB is only for internal storage, for external representation you need
JSON.

Now that I've actually written the question down it occurs to me that what I
really want to know is which data type (JSON or JSONB or maybe TEXT) has the
lowest overhead in terms of transformation and transmission at the PostgreSQL
protocol level.

If you are returning JSON, so is JSON the best choice.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Oct 20, 2015 at 4:04 AM, Andreas Kretschmer <
akretschmer@spamfence.net> wrote:

If you are returning JSON, so is JSON the best choice.

The JSONB type represented as text (i.e., when you query it) is valid JSON.
The same with JSON data type. The DB server renders the data as text when
it sends it to you.

The choice should be made based on what you plant to do with the data in
the database and if the properties of JSON are needed vs. the properties of
JSONB (specifically duplicate key names and ordering of keys) or if it is
not manipulated in the DB ever, then plain text is reasonable too.