> This page location: Postgres guides > Data types > JSON
> Full Neon documentation index: https://neon.com/docs/llms.txt

# 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. They also work well for modeling document-like data typically stored in NoSQL databases.

## Storage and syntax

### JSON

- The `JSON` data type stores `JSON` 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 a `TEXT` column is that Postgres validates the `JSON` data at ingestion time, ensuring it is well-formed.

### JSONB

- The `JSONB` (JSON Binary) data type stores `JSON` 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 the `JSON` data every time it is accessed.

`JSON` values can be created from string literals by casting. For example:

```sql
SELECT
    '{"name": "Alice", "age": 30}'::JSON as col_json,
    '[1, 2, "foo", null]'::JSONB as col_jsonb;
```

This query returns the following:

```text
           col_json           |      col_jsonb
------------------------------+---------------------
 {"name": "Alice", "age": 30} | [1, 2, "foo", null]
```

## Example usage

Say you're 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:

```sql
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile JSONB NOT NULL
);

INSERT INTO user_profiles (profile)
VALUES
    ('{"name": "Alice", "age": 30, "interests": ["music", "travel"], "settings": {"privacy": "public", "notifications": true, "theme": "light"}}'),
    ('{"name": "Bob", "age": 25, "interests": ["photography", "cooking"], "settings": {"privacy": "private", "notifications": false}, "city": "NYC"}'),
    ('{"name": "Charlie", "interests": ["music", "cooking"], "settings": {"privacy": "private", "notifications": true, "language": "English"}}');
```

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:

```sql
SELECT
    id,
    profile -> 'name' as name,
    profile -> 'interests' as interests
FROM user_profiles
WHERE profile @> '{"interests":["music"]}'::JSONB;
```

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:

```text
 id |   name    |      interests
----+-----------+----------------------
  1 | "Alice"   | ["music", "travel"]
  3 | "Charlie" | ["music", "cooking"]
```

Note that the `name` values returned are still in `JSON` format. To extract the value as text, we can use the `->>` operator instead:

```sql
SELECT
    id,
    profile ->> 'name' as name
FROM user_profiles;
```

This query returns the following:

```text
 id |  name
----+---------
  1 | Alice
  2 | Bob
  3 | Charlie
```

## 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](https://neon.com/docs/functions/json_array_elements)
- [jsonb_array_elements](https://neon.com/docs/functions/jsonb_array_elements)
- [json_build_object](https://neon.com/docs/functions/json_build_object)
- [json_each](https://neon.com/docs/functions/json_each)
- [json_extract_path](https://neon.com/docs/functions/json_extract_path)
- [json_extract_path_text](https://neon.com/docs/functions/json_extract_path_text)
- [json_object](https://neon.com/docs/functions/json_object)
- [json_populate_record](https://neon.com/docs/functions/json_populate_record)
- [json_to_record](https://neon.com/docs/functions/json_to_record)

For additional `JSON` operators and functions, refer to the [official PostgreSQL documentation](https://www.postgresql.org/docs/current/functions-json.html)

### 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:

```sql
SELECT
    id,
    profile -> 'name' as name,
    profile -> 'settings' ->> 'privacy' as privacy
FROM user_profiles;
```

This query returns the following:

```text
 id |   name    | privacy
----+-----------+---------
  1 | "Alice"   | public
  2 | "Bob"     | private
  3 | "Charlie" | private
```

### 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`:

```sql
UPDATE user_profiles
SET profile = jsonb_set(profile, '{settings, privacy}', '"friends-only"')
WHERE profile -> 'settings' ->> 'privacy' = 'public';
```

`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.

```sql
CREATE INDEX idxgin ON user_profiles USING GIN (profile);
```

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 use this index.

```sql
SELECT *
FROM user_profiles
WHERE profile @> '{"interests":["music"]}';
```

## 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 within `JSONB` columns faster.
- **Performance**: `JSONB` binary format is more efficient for querying and manipulating, as it doesn't require re-parsing the `JSON` 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

- [PostgreSQL documentation - JSON Types](https://www.postgresql.org/docs/current/datatype-json.html)
- [PostgreSQL documentation - JSON Functions and Operators](https://www.postgresql.org/docs/current/functions-json.html)

---

## Related docs (Data types)

- [Array](https://neon.com/docs/data-types/array)
- [Boolean](https://neon.com/docs/data-types/boolean)
- [Date and time](https://neon.com/docs/data-types/date-and-time)
- [Character](https://neon.com/docs/data-types/character)
- [Decimal](https://neon.com/docs/data-types/decimal)
- [Floating point](https://neon.com/docs/data-types/floating-point)
- [Integer](https://neon.com/docs/data-types/integer)
- [Tsvector](https://neon.com/docs/data-types/tsvector)
- [UUID](https://neon.com/docs/data-types/uuid)