= 1024) leftSidebarOpen = false; if(window.innerWidth >= 1280) rightSidebarOpen = false"<br>:class="{ 'dark': $store.theme.dark, 'left-sidebar-closed': !leftSidebarDesktop, 'right-sidebar-closed': !rightSidebarDesktop, 'overflow-hidden': leftSidebarOpen || rightSidebarOpen, 'sidebars-closed': !leftSidebarDesktop && !rightSidebarDesktop }">

PostgreSQL JSONB: Query, Update, and Index JSON Data

Skip to content

Download Free

0" x-text="$store.bookmarks.count" class="nav-bookmarks-count" x-cloak>

Bookmarks

0">

No bookmarks yet.

Bookmarks are saved locally on this browser.

= 1280 ? rightSidebarDesktop = !rightSidebarDesktop : rightSidebarOpen = true"<br>class="nav-icon-btn"<br>:class="{ 'is-active': !(window.innerWidth >= 1280 ? rightSidebarDesktop : rightSidebarOpen) }"<br>aria-label="Toggle sidebar">

= 1280 ? !rightSidebarDesktop : !rightSidebarOpen" d="M15 3h4a2 2 0 0 1 2 2v14a2 2 0 0 1-2 2h-4V3z" fill="currentColor" stroke="none" />

VisuaLeaf

Download

Navigation

if (window.innerWidth

Search articles…

Preferences

Layout

Save Sidebar view

Remember if sidebars are open or closed across pages.

Save Sidebar layout preference

Enable Bookmarks

Save your favourite articles locally in this browser.

Enable Local Bookmarks

let scroll = window.scrollY;<br>let docHeight = document.documentElement.scrollHeight - window.innerHeight;<br>this.progress = docHeight > 0 ? Math.max(0, Math.min(1, scroll / docHeight)) : 0;<br>this.ticking = false;<br>});<br>}"<br>@scroll.window.passive="update()"<br>@resize.window.passive="update()"<br>x-init="update()"<br>class="fixed top-0 left-0 w-full h-[3px] z-[100] pointer-events-none">

Querying and indexing PostgreSQL JSONB data in VisuaLeaf.

JSONB allows you to store JSON data within a PostgreSQL table row. It is suitable when certain columns remain unchanged, while the remaining columns may have different content across rows.<br>Let us take the support tickets table, where the status, priority, and the date a ticket was created cannot change. However, the client name, environment, tags, and even error details may be optional and have different formats. That is why the optional content may be stored in the JSONB field.<br>This storage process is easy enough. However, you need answers to questions such as how to find a nested value, update a single field, or create an effective index for your database engine.<br>This article explores how to achieve that goal using a support_tickets table and the corresponding details column.<br>PostgreSQL JSONB operations used in this guide

Operation<br>PostgreSQL syntax<br>What it does

Return a JSON object<br>-><br>Keeps the result as JSONB

Extract a text value<br>->><br>Returns a value you can filter or compare

Match part of a document<br>@><br>Checks whether JSONB contains a structure

Update a nested value<br>jsonb_set()<br>Changes one path without replacing the document

Index JSONB searches<br>GIN<br>Speeds up supported JSONB queries

Check index usage<br>EXPLAIN<br>Shows how PostgreSQL runs the query

We’ll use each of these against the same support_tickets.details column, so you can see how querying, updating, and indexing work together.<br>Create the support tickets table<br>CREATE TABLE support_tickets (<br>ticket_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,<br>status TEXT NOT NULL,<br>priority TEXT NOT NULL,<br>created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),<br>details JSONB NOT NULL<br>CHECK (jsonb_typeof(details) = 'object')<br>);<br>I kept status, priority, and created_at outside JSONB because they have stable types and are useful for filtering and sorting. The less predictable ticket context goes into details.<br>Here is one record used in the test:<br>INSERT INTO support_tickets (status, priority, details)<br>VALUES (<br>'open',<br>'high',<br>'{<br>"customer": {<br>"name": "Trevor Lisbon",<br>"plan": "Professional"<br>},<br>"environment": {<br>"browser": "Chrome",<br>"os": "Windows 11",<br>"appVersion": "4.8.2"<br>},<br>"tags": ["sync", "postgresql"],<br>"error": {<br>"code": "SYNC_TIMEOUT",<br>"retryable": true<br>}'::jsonb<br>);<br>The CHECK constraint confirms that details contains a JSON object. It does not guarantee that customer.plan exists or that tags is always an array. JSONB validates the JSON format, not your complete application schema.<br>PostgreSQL table with regular columns and nested JSONB data displayed in VisuaLeaf.Query nested JSONB values<br>PostgreSQL provides two operators that look similar but return different data types:

Operator<br>Returns<br>Example

-><br>JSONB<br>{"name": "Trevor Lisbon", "plan": "Professional"}

->><br>Text<br>Professional

Use -> when you want an object or array to remain JSONB.<br>Use ->> when you need a scalar value for an ordinary SQL comparison.<br>This query returns the complete customer object and extracts the plan as text:<br>SELECT<br>ticket_id,<br>details -> 'customer' AS customer,<br>details -> 'customer' ->> 'plan' AS plan<br>FROM support_tickets<br>WHERE ticket_id Expanding the customer JSONB object while plan is returned as text.A common mistake is to use -> for the final value:<br>WHERE details -> 'customer' -> 'plan' = 'Professional'<br>The left side is JSONB, while Professional is...