JSON/JSONB data types

Eugenia Kuzina

PostgreSQL offers two types to store JSON data defined in RFC 7159: JSON and JSONB. To implement efficient query mechanisms for these data types, PostgreSQL also provides the JSONPath data type.

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:

  • JSONB requires more time to build from its input representation.

  • JSONB operations take significantly less time than JSON operations. JSON processing functions reparse data on each execution, while JSONB stores parsed values.

  • JSONB supports indexes, JSON does 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

JSONB does not allow \u0000 and Unicode escapes of characters that are not supported by the database encoding

number

numeric

The NaN and infinity values are not allowed

boolean

boolean

Only lowercase true and false spellings are accepted

null

 — 

This is not SQL null

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, or null. 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"     +
 }
Found a mistake? Seleсt text and press Ctrl+Enter to report it