JSON/JSONB data types
JSON and JSONB data types
Both JSON and JSONB allow you to store valid JSON values. JSON saves data in a plain text format, as a copy of the input text. JSONB stores data in a decomposed binary format. These formats have the following differences:
-
JSONBrequires more time to build from its input representation. -
JSONBoperations take significantly less time thanJSONoperations.JSONprocessing functions reparse data on each execution, whileJSONBstores parsed values. -
JSONBsupports indexes,JSONdoes not.
Use JSON if you work with the JSON representation, and PostgreSQL is only used to store and retrieve JSON data. If you do a lot of operations in PostgreSQL, or use indexing on JSON fields, use JSONB. For more information on indexes, refer to jsonb Indexing to efficiently query JSON data stored in the JSONB format.
JSON also preserves semantically-insignificant white spaces between tokens and the order of keys within JSON objects. If a JSON object within the value contains the same key more than once, all the key/value pairs are kept. The processing functions consider the last value as the operative one.
JSONB does not preserve white spaces, the order of object keys, and does not keep duplicate object keys. If duplicate keys exist in the input, only the last value is kept.
JSON and JSONB encoding
RFC 7159 specifies that JSON strings should be encoded in UTF8. It is possible only if the database encoding is UTF8. You cannot use characters that cannot be represented in the database encoding. Non-UTF8 characters supported by the database encoding are allowed.
RFC 7159 permits JSON strings to contain Unicode escape sequences denoted by \uXXXX. The input function for the JSON type allows Unicode escapes regardless of the database encoding. Only their syntax is checked: whether four hex digits follow \u.
The input JSONB function prohibits Unicode escapes for characters that are not allowed by the database encoding. JSONB also rejects \u0000 (NULL), because it cannot be represented in the PostgreSQL text type, and validates whether any use of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual Plane (BMP) is correct. The input JSONB function converts valid Unicode escapes to the equivalent single character and concatenates surrogate pairs into a single character.
Many of the JSON processing functions convert Unicode escapes to regular characters, and throw errors. The fact that the JSON input function does not validate the input characters only allows you to store (without processing) Unicode escapes to a database encoding that does not support these characters.
JSONB converting
When converting text JSON input into JSONB, the primitive types are mapped onto native PostgreSQL types, as shown in the table below. JSONB has some minor additional constraints, corresponding to restrictions of the PostgreSQL data types. For example, JSONB rejects numbers that are outside the range of the PostgreSQL numeric data type.
| JSON primitive type | PostgreSQL type | Notes |
|---|---|---|
string |
text |
|
number |
numeric |
The |
boolean |
boolean |
Only lowercase |
null |
— |
This is not SQL |
JSON and JSONB syntax
The following JSON expressions are valid for the JSON and JSONB types:
-
Simple scalar/primitive values. Primitive values can be numbers, quoted strings,
true,false, ornull. Example:SELECT '5'::json; SELECT '"Text string"'::json; -
Array of zero or more elements. Array can contain elements of different types. Example:
SELECT '[0, 1, "array element", null]'::json; -
Objects that contain pairs of keys and values. Key is a quoted string. Example:
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json; -
Nested arrays and objects. Example:
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
Store JSON data to a table
To store JSON data to a table, use a JSONB or JSON column. For example, we create a new table book_orders with a JSONB column:
CREATE TABLE book_orders (
id serial NOT NULL PRIMARY KEY,
content jsonb NOT NULL
);To insert data into a table, ensure that data is in a valid JSON format. The following code adds new rows to the book_orders table:
INSERT INTO book_orders (content)
VALUES('{ "customer": "Jacob Johnson", "items": {"book": "Hyperion","qty": 3}}'),
('{ "customer": "Adam Brown", "items": {"book": "War and Peace","qty": 2}}'),
('{ "customer": "Andrew Nelson", "items": {"book": "1984","qty": 4}}');Use operators to query JSON data
You can use the SELECT statement to get JSON data in the same way as you operate with other data types:
SELECT content FROM book_orders;The result:
content
--------------------------------------------------------------------------
{"items": {"qty": 2, "book": "War and Peace"}, "customer": "Adam Brown"}
{"items": {"qty": 4, "book": "1984"}, "customer": "Andrew Nelson"}
{"items": {"qty": 3, "book": "Hyperion"}, "customer": "Jacob Johnson"}
PostgreSQL also provides operators to work with JSON data types. For example, the -> operator returns a JSON object by key, ->> returns a JSON object by key as text.
The following query uses the -> operator to get all customers as JSON objects:
SELECT content -> 'customer' AS customer
FROM book_orders;The result:
customer ----------------- "Jacob Johnson" "Adam Brown" "Andrew Nelson
The query below uses the ->> operator to get all customers in the text form:
SELECT content ->> 'customer' AS customer
FROM book_orders;The result:
customer --------------- Jacob Johnson Adam Brown Andrew Nelson
Since the -> operator returns a JSON object, you can use it with the ->> operator to retrieve a specific node. For example, the following statement returns all books:
SELECT content -> 'items'->> 'book' AS book
FROM book_orders;The result:
book --------------- War and Peace 1984 Hyperion
content -> 'items' returns items as JSON objects. content->'items'->>'book' returns all books as text.
It is also possible to get a JSON object at the specified path. To do this, use the #> operator.
The following statement returns JSON objects at the path items→book:
SELECT content #> '{items, book}' AS books
FROM book_orders;The result:
books ----------------- "War and Peace" "1984" "Hyperion"
You can also return JSON objects at the specified path as text. To do this, use the #>> operator:
SELECT content #>> '{items, book}' AS books
FROM book_orders;The result:
books --------------- War and Peace 1984 Hyperion
Use JSON operators in the WHERE clause
You can include JSON operators in the WHERE clause to filter rows. For example, the following query returns a customer who bought the "War and Peace" book:
SELECT content ->> 'customer' AS customer
FROM book_orders
WHERE content -> 'items' ->> 'book' = 'War and Peace';The result:
customer ------------ Adam Brown
Utilize a type cast in the WHERE clause to use comparison operators. In the query below, the type cast is used to convert the qty value into the INTEGER type and compare it with 3:
SELECT content ->> 'customer' AS customer,
content -> 'items' ->> 'book' AS book
FROM book_orders
WHERE CAST ( content -> 'items' ->> 'qty' AS INTEGER) = 3;The result:
customer | book ---------------+---------- Jacob Johnson | Hyperion
Contain and exist operators
It is possible to rewrite the first WHERE clause example with the additional JSONB operator @> that determines whether the first JSON value contains the second one.
SELECT content ->> 'customer' AS customer
FROM book_orders
WHERE content -> 'items' -> 'book' @> '"War and Peace"'::jsonb;The result is the same:
customer ------------ Adam Brown
The @> operator takes into account the element nesting level.
For example, the following statement returns true:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;However, the example below returns false:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;It is necessary to explicitly specify the nesting level to get the true result:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;The ? operator is a variation of @>. It determines whether a JSONB object contains the specified string as an object key or array element at the top level. The following statement returns true:
SELECT content ? 'customer'
FROM book_orders;For more information on additional JSONB operators, refer to Additional JSONB operators.
Apply aggregate functions to JSON data
Aggregate functions (min. max, avg, and others) can be applied to JSON data. For example, the following statement returns minimum, maximum, average, and the total quantity of books. The example also uses the ROUND function to round the result of the AVG function and CAST to cast the input data type to an appropriate type for aggregate functions.
SELECT
ROUND(AVG (CAST (content -> 'items' ->> 'qty' AS INTEGER)),2) AS AVG,
MIN (CAST (content -> 'items' ->> 'qty' AS INTEGER)),
MAX (CAST (content -> 'items' ->> 'qty' AS INTEGER)),
SUM (CAST (content -> 'items' ->> 'qty' AS INTEGER))
FROM book_orders;The result:
avg | min | max | sum ------+-----+-----+----- 3.00 | 2 | 4 | 9
PostgreSQL functions to process the JSON and JSONB types
PostgreSQL provides a lot of functions to process JSON/JSONB data, some of them are described below.
json_each/jsonb_each
The json_each and jsonb_each functions expand the top-level JSON object into a set of key/value pairs.
The functions have the following syntax:
json_each (<json>) → setof record (key text, value json) jsonb_each (<jsonb>) → setof record (key text, value jsonb)
Example:
SELECT jsonb_each(content)
FROM book_orders;The result:
jsonb_each
-----------------------------------------------------
(items,"{""qty"": 2, ""book"": ""War and Peace""}")
(customer,"""Adam Brown""")
(items,"{""qty"": 4, ""book"": ""1984""}")
(customer,"""Andrew Nelson""")
(items,"{""qty"": 3, ""book"": ""Hyperion""}")
(customer,"""Jacob Johnson""")
To get a set of key/value pairs as text, use the json_each_text or jsonb_each_text function.
json_object_keys/jsonb_object_keys
The json_object_keys and jsonb_object_keys functions return a set of keys of the top-level JSON object.
The functions have the following syntax:
json_object_keys(<json>) → setof text jsonb_object_keys(<jsonb>) → setof text
The statement below displays a set of keys for the items objects:
SELECT jsonb_object_keys (content->'items')
FROM book_orders;The result:
jsonb_object_keys ------------------- qty book qty book qty book
json_typeof/jsonb_typeof
The json_typeof and jsonb_typeof functions return a type of the top-level JSON value as a string. They can return the number, boolean, null, object, array, and string values.
The functions have the following syntax:
json_typeof(<json>) → setof text jsonb_typeof(<jsonb>) → setof text
The statement below displays the type of the qty objects:
SELECT jsonb_typeof (content->'items'->'qty')
FROM book_orders;The result:
jsonb_typeof -------------- number number number
json_extract_path/jsonb_extract_path
The json_extract_path and jsonb_extract_path functions extract JSON objects at the specified path. It is equivalent to the #> operator.
The functions have the following syntax:
json_extract_path ( <from_json> json, <VARIADIC path_elems> text[] ) → json jsonb_extract_path ( <from_json> jsonb, <VARIADIC path_elems> text[] ) → jsonb
The statement below displays qty objects:
SELECT jsonb_extract_path (content, 'items','qty')
FROM book_orders;The result:
jsonb_extract_path -------------------- 2 4 3
jsonb_pretty
The jsonb_pretty function converts a JSON value to the pretty-printed, indented text.
The function has the following syntax:
jsonb_pretty (<jsonb>) → text
The following example displays the items objects:
SELECT jsonb_pretty(content->'items')
FROM book_orders;The result:
jsonb_pretty
-----------------------------
{ +
"qty": 2, +
"book": "War and Peace"+
}
{ +
"qty": 4, +
"book": "1984" +
}
{ +
"qty": 3, +
"book": "Hyperion" +
}