Storing, Accessing, and Retrieving JSON Data in PostgreSQL: A Practical Guide

What is JSON: The Universal Language of Data Exchange

In today’s digital world, applications constantly communicate with each other—mobile apps talk to servers, websites fetch data from APIs, and backend systems integrate with third‑party services. At the heart of much of this communication is JSON, short for JavaScript Object Notation. Despite its name, JSON is not limited to JavaScript; it has become a universal, language‑independent format for storing and exchanging data.

JSON is popular because it is both human‑readable and machine‑friendly. Its structure is simple: data is represented using key–value pairs, arrays, numbers, strings, booleans, and null values.

A typical JSON object looks like this:

{

"color": "black",

"sizes": [7, 8, 9],

"in_stock": true

}

This simplicity makes JSON easy to understand, even for beginners.

One of the biggest reasons JSON has become a standard is its flexibility. Unlike rigid relational tables, JSON allows dynamic structures. You can add new fields without altering database schemas or breaking existing applications. This makes it ideal for modern applications that evolve quickly.

In short, JSON has become the backbone of modern data exchange. Its readability, versatility, and widespread support make it an essential tool for developers building scalable and adaptable systems.

Need for JSON and its usage in PostgreSQL

Modern applications generate and consume more semi‑structured data than ever before. Whether it’s API responses, configuration objects, logs, or user‑generated content, JSON has become the universal language for flexible data exchange. PostgreSQL — long known for its reliability and SQL power — has evolved into one of the best databases for working with JSON natively.

In this article, we’ll explore how PostgreSQL stores JSON, how to insert and query it efficiently, and why developers increasingly rely on PostgreSQL as a hybrid relational + document database.

JSON Datatype in PostgreSQL

PostgreSQL supports two JSON data types:

• json — stores JSON text exactly as provided

• jsonb — stores JSON in a binary, decomposed format for faster querying

  
  

Most developers choose jsonb because it offers indexing, faster search, and better performance for real‑world workloads.

Step 1: Creating a Table with JSONB datatype

Let’s start with a simple example. Suppose you’re building a product catalog where each product has dynamic attributes such as color, size, or tags.

Here, attributes can store any JSON object.

Step 2: Inserting JSON Data

PostgreSQL accepts JSON in plain text form.

You can store nested objects, arrays, booleans, and numbers — all valid JSON.

Fetching data from Products Table:

Step 3: Retrieving JSON Data

PostgreSQL provides intuitive operators to extract values from JSON.

1. Using the → operator (returns JSON)

-> operator: Extracts a JSON object field (key) or array element, returning the result as JSON or JSONB data type

2. Using the →> operator (returns text)

->> operator: Extracts a JSON object field or array element as plain text. 

3. Accessing array/nested values.

This retrieves the first size in the array.

Step 4: Filtering Data Based on JSON Fields

Writing a query to find products that are in stock.

Writing a query to find products that include size 9.

Operator @> refers to array contains

Updating JSON Fields

PostgreSQL allows partial updates using the jsonb_set function.

Writing a query to update the object color in the JSONB column.

Fetching the data from Products table after update execution, the color attribute value got changed from black to blue.

This updates only the color field without replacing the entire JSON object.

Indexing JSON for Better Performance

If your application frequently queries JSON fields, indexing is essential.

Writing a query to create a GIN index in JSONB column

A GIN index (Generalized Inverted Index) is a special type of index in PostgreSQL designed for fast searching inside complex data structures, GIN indexes make JSON queries significantly faster, especially for large datasets.

Delete a key from a JSONB object

Use the - operator.

1. Writing a query to remove the key "color" from the attributes  JSONB column.

2.  Delete an element from a JSONB array

      Use the - operator with an index.

This removes the element at index 1.

3. Writing a query to delete multiple elements from JSONB array

JSON Operators

Operators offer a concise way to interact with JSON data in queries. 

• ->: Extracts a JSON object field by key or array element by index, returning the result as jsonb (or json).

  • SELECT data->'address' FROM users;

• ->>: Extracts a JSON object field or array element as text.

  • SELECT data->>'email' FROM users;

• @>: Checks if the first JSON value contains the second (containment operator).

  • SELECT * FROM users WHERE data @> '{"age": 30}';

• ?: Checks for the existence of a top-level key or array element (as a text string).

  • SELECT * FROM users WHERE data ? 'age';

• ||: Concatenates two jsonb values.

  • SELECT data || '{"verified": false}' AS updated_data FROM users;

• -: Deletes a key from a JSON object, or matching string value(s) from a JSON array.

  • SELECT data - 'age' AS partial_data FROM users;

    
    

Core JSON Functions

PostgreSQL provides numerous functions for more complex operations. 

a. Creation Functions

• to_json(anyelement) / to_jsonb(anyelement): Converts any SQL value (including rows and arrays) to JSON or JSONB.

• json_build_object(variadic any) / jsonb_build_object(...): Builds a JSON object from a variadic list of key-value pairs.

• json_object(text[]) / jsonb_object(text[]): Builds a JSON object from a text array of alternating keys and values or a two-dimensional array.

• row_to_json(record): Converts an SQL composite value (row) to a JSON object. 

  
  

b. Processing/Manipulation Functions

• json_each(json) / jsonb_each(jsonb): Expands the top-level JSON object into a set of key/value pairs.

• json_object_keys(json) / jsonb_object_keys(jsonb): Returns the set of keys in the top-level JSON object.

• jsonb_set(target, path, new_value [, create_if_missing]): Returns the target jsonb with the item specified by path replaced by new_value, adding it if it doesn't exist (by default).

• jsonb_insert(target, path, new_value [, insert_after]): Inserts a new value into a jsonb array or object. For objects, it only inserts if the key does not already exist.

• jsonb_strip_nulls(from_json jsonb): Recursively deletes all object fields that have null values.

• jsonb_pretty(jsonb): Converts the given jsonb value to indented, human-readable text format.

• json_typeof(json) / jsonb_typeof(jsonb): Returns the type of the top-level JSON value as a text string (e.g., object, array, string, number, boolean, null). 

  
  

c. SQL/JSON Path Functions

These functions use the SQL/JSON path language expressions for powerful data querying, especially with jsonb: 

• jsonb_path_query(target, path [, vars, silent]): Returns all JSON items returned by the JSON path expression as a set of jsonb values.

• jsonb_path_exists(target, path [, vars, silent]): Checks if the JSON path returns any item for the specified JSON value. 

When to Use JSON in PostgreSQL

JSON is ideal when:

• Your data structure changes frequently

• You need to store flexible metadata

• You want NoSQL‑like behavior without leaving SQL

• You’re integrating with APIs that return JSON

However, for core relational data, traditional columns still offer better performance and clarity.

Conclusion

PostgreSQL’s JSON and JSONB support gives developers the best of both worlds: the flexibility of a document store and the power of a relational database. Whether you’re building microservices, analytics platforms, or e‑commerce systems, PostgreSQL makes it easy to store, query, and manipulate JSON efficiently.

By mastering JSON operators, indexing strategies, and update functions, you can design modern, scalable applications without sacrificing structure or performance.