Storing & Retrieving JSON Data

PostgreSQL has first-class support for JSON data, making it one of the most versatile databases available. You can combine the reliability and power of a relational database with the flexibility of document storage.

JSON vs JSONB

PostgreSQL offers two JSON data types:

Recommendation: Almost always use JSONB. The query performance and indexing capabilities far outweigh the slightly slower insert time.

The user_settings table uses JSONB for both its preferences and profile columns:

SELECT username, preferences, profile
FROM user_settings;

The -> Operator (Get JSON Object)

The -> operator extracts a JSON value and returns it as JSONB (preserving the JSON type).

-- Get the preferences object's "theme" key (returns JSONB)
SELECT
  username,
  preferences -> 'theme' AS theme_json
FROM user_settings;

The result type is JSONB, meaning it includes quotes around strings: "dark".

Accessing by Integer Index (Arrays)

If your JSON contains arrays, use -> with an integer to access elements by position (0-based):

-- If preferences had an array, you'd access it like:
-- preferences -> 'tags' -> 0

The ->> Operator (Get as Text)

The ->> operator extracts a value and returns it as plain text. This is what you usually want for display or comparison.

-- Get the theme as text (no quotes)
SELECT
  username,
  preferences ->> 'theme' AS theme
FROM user_settings;

When to Use -> vs ->>

• Use -> when you need to chain further JSON operations (returns JSONB)
• Use ->> when you need the final text value for display or comparison

Chaining Operators for Nested Access

The preferences column contains nested objects like:

{
  "theme": "dark",
  "notifications": {
    "email": true,
    "push": false
  },
  "editor": {
    "font_size": 14,
    "tab_size": 4
  }
}

Chain -> to navigate into nested structures, and use ->> at the end:

-- Get the nested email notification setting
SELECT
  username,
  preferences -> 'notifications' ->> 'email' AS email_notifications
FROM user_settings;

-- Get the editor font size
SELECT
  username,
  preferences -> 'editor' ->> 'font_size' AS font_size
FROM user_settings;

Note: Even though font_size is a number in JSON, ->> returns it as text. To use it as a number, cast it:

SELECT
  username,
  (preferences -> 'editor' ->> 'font_size')::int AS font_size
FROM user_settings
WHERE (preferences -> 'editor' ->> 'font_size')::int > 12;

The #> and #>> Path Operators

For deeply nested access, the path operators provide a cleaner syntax. Instead of chaining ->, you pass a text array representing the path.

-- #> returns JSONB (same as chaining ->)
SELECT
  username,
  preferences #> '{notifications,email}' AS email_notif_json
FROM user_settings;

-- #>> returns text (same as ending chain with ->>)
SELECT
  username,
  preferences #>> '{notifications,email}' AS email_notif
FROM user_settings;

Both of these are equivalent to preferences -> 'notifications' -> 'email' and preferences -> 'notifications' ->> 'email' respectively.

Path Operator Advantages

Path operators shine when navigating three or more levels deep:

-- Instead of:
SELECT preferences -> 'editor' -> 'settings' ->> 'auto_save'
FROM user_settings;

-- Use:
SELECT preferences #>> '{editor,settings,auto_save}'
FROM user_settings;

The @> Containment Operator

The @> operator checks whether the left JSONB value contains the right JSONB value. This is one of the most powerful JSONB features.

-- Find users with dark theme
SELECT username, preferences
FROM user_settings
WHERE preferences @> '{"theme": "dark"}';

You can check for nested values too:

-- Find users with email notifications enabled
SELECT username
FROM user_settings
WHERE preferences @> '{"notifications": {"email": true}}';

Containment Works with Partial Matching

The left side only needs to contain the right side -- it can have additional keys:

-- This matches even though preferences has many other keys
WHERE preferences @> '{"theme": "dark"}'

Containment is Indexable

A GIN index makes @> extremely fast on large datasets:

CREATE INDEX idx_settings_prefs ON user_settings USING gin (preferences);

The ? Existence Operator

The ? operator checks if a key exists in a JSONB object (or a string exists in a JSONB array).

-- Find users whose preferences have a "theme" key
SELECT username
FROM user_settings
WHERE preferences ? 'theme';

Related Operators

-- Users with any of these preference keys
SELECT username
FROM user_settings
WHERE preferences ?| array['theme', 'language'];

-- Users with ALL of these preference keys
SELECT username
FROM user_settings
WHERE preferences ?& array['theme', 'notifications', 'editor'];

Filtering with JSON Values

Using ->> in WHERE Clauses

-- Find users with dark theme
SELECT username, preferences
FROM user_settings
WHERE preferences ->> 'theme' = 'dark';

Comparing Numeric JSON Values

Cast the text result to a number for numeric comparisons:

-- Users with font size greater than 12
SELECT username, preferences -> 'editor' ->> 'font_size' AS font_size
FROM user_settings
WHERE (preferences -> 'editor' ->> 'font_size')::int > 12;

Checking for NULL/Missing Keys

-- Users where the "language" key exists in preferences
SELECT username
FROM user_settings
WHERE preferences -> 'language' IS NOT NULL;

-- Users where the "language" key does NOT exist
SELECT username
FROM user_settings
WHERE preferences -> 'language' IS NULL;

Try It Yourself

1. Select all usernames and their theme preference (as text) from user_settings.

2. Find all users whose preferences contain email notifications set to true. Use the @> containment operator.

3. Extract the font_size from the nested editor object in preferences. Cast it to an integer and filter for sizes greater than 12.

4. Use the #>> path operator to extract the push notification setting from preferences -> 'notifications' -> 'push'.