Database Reference
In-Depth Information
bers can appear slightly different, and attributes become sorted. For example, a
number input as
e-5
would be converted to its decimal representation.
•
jsonb
does not allow duplicate keys and silently picks one, whereas the
json
type
preserves duplicates. This is demonstrated in
Michael Paquier: Manipulating jsonb
•
jsonb
performance is much better than
json
performance because
jsonb
doesn't
need to be reparsed during operations.
•
jsonb
columns can be directly indexed using the GIN index method (covered in
“Indexes” on page 112
, whereas
json
requires a functional index to extract key ele‐
ments.
To demonstrate these concepts, we'll create another
families
table, replacing the
json
column with a
jsonb
:
CREATE
TABLE
families_b
(
id
serial
PRIMARY
KEY
,
profile
jsonb
);
To insert data into our new table, we would repeat
Example 5-23
.
So far, working with JSON and binary JSON has been the same. Differences appear
when you query. To make the binary JSON readable, PostgreSQL converts it to a can‐
onical text representation, as shown in
Example 5-28
.
Example 5-28. jsonb vs. json output
SELECT
profile
As
b
FROM
families_b
WHERE
id
=
1
;
SELECT
profile
As
j
FROM
families_j
WHERE
id
=
1
;
b
--------------------------------------------------------------------------------
{"name": "Gomez", "members": [{"member": {"name": "Alex", "relation": "padre"}}
, {"member": {"name": "Sonia", "relation": "madre"}}, {"member": {"name": "Brand
on", "relation": "hijo"}}, {"member": {"name": "Azaleah", "relation": "hija"}}]}
j
---------------------------------------------------------------------------
{"name":"Gomez","members":[{"member":{"relation":"padre", "name":"Alex"}},
{"member":{"relation":"madre", "name":"Sonia"}},
{"member":{"relation":"hijo", "name":"Brandon"}},
{"member":{"relation":"hija", "name":"Azaleah"}}]}
jsonb
reformats input and removes whitespace. Also, the order of relation and
name attributes is flipped from their original order.
json
maintains input whitespace and the order of attributes.
jsonb
has similarly named functions and the same-named operators as
json
, plus some
additional ones. So, for example, the
json
family of functions such as
json_ex
tract_path_text
and
json_each
are matched in
jsonb
by
jsonb_ex
tract_path_text
,
jsonb_each
, etc. However, the equivalent operators are the same, so
