Querying Nested JSON Data

Now that you know the basic operators (->, ->>, #>, @>), let's explore PostgreSQL's rich set of JSON functions for working with complex, nested data structures.

jsonb_extract_path() and jsonb_extract_path_text()

These functions are the functional equivalents of the #> and #>> operators. They navigate a path through a JSONB document.

jsonb_extract_path() -- Returns JSONB

-- Equivalent to: preferences #> '{notifications}'
SELECT
  username,
  jsonb_extract_path(preferences, 'notifications') AS notif_settings
FROM user_settings;

jsonb_extract_path_text() -- Returns Text

-- Equivalent to: preferences #>> '{notifications,email}'
SELECT
  username,
  jsonb_extract_path_text(preferences, 'notifications', 'email') AS email_notif
FROM user_settings;

Why Use Functions Instead of Operators?

The function form has a few advantages:

1. Readability with deep paths: Comma-separated arguments can be clearer than array syntax
2. Dynamic paths: Easier to construct in procedural code
3. Standard SQL: More portable across database systems

-- Compare readability for a deep path:
-- Operator style:
SELECT preferences #>> '{editor,font_size}' FROM user_settings;

-- Function style:
SELECT jsonb_extract_path_text(preferences, 'editor', 'font_size') FROM user_settings;

jsonb_object_keys()

This set-returning function expands all top-level keys of a JSONB object into separate rows.

-- List all top-level preference keys for each user
SELECT
  username,
  jsonb_object_keys(preferences) AS pref_key
FROM user_settings;

Finding Users with Specific Keys

-- Which users have an "editor" key in their preferences?
SELECT DISTINCT username
FROM user_settings, jsonb_object_keys(preferences) AS k
WHERE k = 'editor';

Counting Keys per User

SELECT
  username,
  COUNT(k) AS num_preference_keys
FROM user_settings, jsonb_object_keys(preferences) AS k
GROUP BY username;

jsonb_each() and jsonb_each_text()

These functions expand a JSONB object into a set of key-value pair rows.

jsonb_each() -- Values as JSONB

SELECT
  username,
  kv.key,
  kv.value
FROM user_settings,
     jsonb_each(preferences) AS kv;

jsonb_each_text() -- Values as Text

SELECT
  username,
  kv.key,
  kv.value
FROM user_settings,
     jsonb_each_text(preferences) AS kv;

The values are now plain text strings. Nested objects are converted to their text representation.

Using jsonb_each for Flexible Queries

This is powerful for searching across all settings:

-- Find any preference key that has the value "dark"
SELECT username, kv.key, kv.value
FROM user_settings,
     jsonb_each_text(preferences) AS kv
WHERE kv.value = 'dark';

jsonb_array_elements()

If your JSONB data contains arrays, jsonb_array_elements() expands each array element into a separate row.

While the user_settings table may not have arrays in its current structure, here is how it works conceptually:

-- Expanding a JSON array literal
SELECT jsonb_array_elements('[1, 2, 3, "four", {"five": 5}]'::jsonb);

jsonb_array_elements_text()

Returns array elements as text instead of JSONB:

SELECT jsonb_array_elements_text('["apple", "banana", "cherry"]'::jsonb);

Practical Use: Querying Array Contents

If a user's profile had a tags array:

-- Hypothetical: find users with a specific tag
SELECT username
FROM user_settings,
     jsonb_array_elements_text(profile -> 'tags') AS tag
WHERE tag = 'admin';

jsonb_typeof()

Returns the type of a JSONB value as a text string. Useful for inspecting unknown structures.

SELECT
  username,
  jsonb_typeof(preferences) AS prefs_type,
  jsonb_typeof(preferences -> 'theme') AS theme_type,
  jsonb_typeof(preferences -> 'notifications') AS notif_type,
  jsonb_typeof(preferences -> 'editor' -> 'font_size') AS font_size_type
FROM user_settings;

Possible return values: object, array, string, number, boolean, null.

Combining Functions for Deep Analysis

Exploring All Nested Keys

You can combine jsonb_each with lateral joins to explore nested structures:

-- Get all top-level keys and the type of each value
SELECT
  username,
  kv.key AS setting_name,
  jsonb_typeof(kv.value) AS value_type,
  CASE
    WHEN jsonb_typeof(kv.value) = 'object'
    THEN (SELECT count(*) FROM jsonb_object_keys(kv.value))
    ELSE 0
  END AS nested_key_count
FROM user_settings,
     jsonb_each(preferences) AS kv;

Flattening Nested JSON

Turn the notification settings into columns:

SELECT
  username,
  preferences ->> 'theme' AS theme,
  preferences -> 'notifications' ->> 'email' AS email_notif,
  preferences -> 'notifications' ->> 'push' AS push_notif,
  preferences -> 'editor' ->> 'font_size' AS font_size,
  preferences -> 'editor' ->> 'tab_size' AS tab_size
FROM user_settings;

This transforms nested JSON into a traditional tabular format, which is useful for reporting.

Searching Across All Nested Values

-- Find any user who has a 'true' value anywhere in their notification settings
SELECT DISTINCT username
FROM user_settings,
     jsonb_each_text(preferences -> 'notifications') AS kv
WHERE kv.value = 'true';

jsonb_to_record() -- JSON to Row Type

Convert a JSONB object directly into a row with typed columns:

SELECT
  username,
  prefs.*
FROM user_settings,
     jsonb_to_record(preferences) AS prefs(
       theme text,
       notifications jsonb
     );

This is powerful when you want to project specific keys from a JSONB column into proper typed columns.

Try It Yourself

1. Use jsonb_extract_path_text to get the font_size from the editor object inside preferences for all users.

2. Use jsonb_object_keys() to list all top-level keys in the preferences column. How many unique keys are there across all users?

3. Use jsonb_each() to expand all preference key-value pairs for user 'alice'. What types of values does she have?

4. Use jsonb_typeof() to check the types of the top-level values in preferences. Which keys have object values vs simple scalar values?