Thread: Returning JSON or JSONB
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?
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?
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.
Thanks,
Dane
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°
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.