Postgres JSON data types
Model JSON data in Postgres
Postgres supports JSON (JavaScript Object Notation) data types, providing a flexible way to store and manipulate semi-structured data. The two types are JSON
and JSONB
. The functions work similarly, but there are trade-offs related to data ingestion and querying performance.
JSON
and JSONB
are ideal for storing data that doesn't fit neatly into a traditional relational model, since new fields can be added without altering the database schema. Additionally, they can also be used to model document-like data typically stored in NoSQL databases.
Storage and syntax
JSON
- The
JSON
data type storesJSON
data in text format. - It preserves an exact copy of the original
JSON
input, including whitespace and ordering of object keys. - An advantage over storing
JSON
data in aTEXT
column is that Postgres validates theJSON
data at ingestion time, ensuring it is well-formed.
JSONB
- The
JSONB
(JSON Binary) data type storesJSON
data in a decomposed binary format. - Unlike
JSON
,JSONB
does not preserve whitespace or the order of object keys. For duplicate keys, only the last value is stored. JSONB
is more efficient for querying, as it doesn't require re-parsing theJSON
data every time it is accessed.
JSON
values can be created from string literals by casting. For example:
This query returns the following:
Example usage
Consider the case of managing user profiles for a social media application. Profile data is semi-structured, with a set of fields common to all users, while other fields are optional and may vary across users. JSONB
is a good fit for this use case.
Using the query below, we can create a table to store user profiles:
With JSONB
, we can directly query and manipulate elements within the JSON
structure. For example, to find all the users interested in music, we can run the query:
The @>
operator checks if the left JSONB
operand contains the right JSONB
operand as a subset. While the ->
operator extracts the value of a JSON
key as a JSON
value.
This query returns the following:
Note that the name
values returned are still in JSON
format. To extract the value as text, we can use the ->>
operator instead:
This query returns the following:
JSON functions and operators
Postgres implements several functions and operators for querying and manipulating JSON
data, including these functions described in the Neon documentation:
- json_array_elements
- jsonb_array_elements
- json_build_object
- json_each
- json_extract_path
- json_extract_path_text
- json_object
- json_populate_record
- json_to_record
For additional JSON
operators and functions, refer to the official PostgreSQL documentation
Nested data
Postgres supports storing nested JSON
values. For example, in the user profile table, the settings
field is a JSON
object itself. The nested values can be extracted by chaining the ->
operator.
For example, to access the privacy
setting for all users, you can run the query:
This query returns the following:
Modifying JSONB data
The JSONB
type supports updating individual fields. For example, the query below sets the privacy
setting for all public users to friends-only
:
jsonb_set
is a Postgres function that takes a JSONB
value, a path to the field to update, and the new value. The path is specified as an array of keys.
Field updates are not supported for the JSON
type.
Indexing JSONB data
Postgres supports GIN (Generalized Inverted Index) indexes for JSONB
data, which can improve query performance significantly.
This makes evaluation of key-exists (?)
and containment (@>)
operators efficient. For example, the query to fetch all users who have music as an interest can leverage this index.
Additional considerations
JSON vs JSONB
JSONB
is the recommended data type for storing JSON
data in Postgres for a few reasons.
- Indexing:
JSONB
allows for the creation of GIN (Generalized Inverted Index) indexes, which makes searching withinJSONB
columns faster. - Performance:
JSONB
binary format is more efficient for querying and manipulating, as it doesn't require re-parsing theJSON
data for each access. It also supports in-place updates to individual fields. - Data integrity:
JSONB
ensures that keys in an object are unique.
There might be some legacy use cases where preserving the exact format of the JSON
data is important. In such cases, the JSON
data type can be used.
Resources
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.