First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too).
Both json & jsonb allows you to store a valid JSON value (defined in its spec).
F.ex. these are valid JSON representations: null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]} - hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it's fine).
The only difference between json & jsonb is their storage:
jsonis stored in its plain text format, whilejsonbis stored in some binary representation
There are 3 major consequences of this:
jsonbusually takes more disk space to store thanjson(sometimes not)jsonbtakes more time to build from its input representation thanjsonjsonoperations take significantly more time thanjsonb(& parsing also needs to be done each time you do some operation at ajsontyped value)
When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:
- If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use
json. - If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use
jsonb.
Top answer 1 of 9
657
First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too).
Both json & jsonb allows you to store a valid JSON value (defined in its spec).
F.ex. these are valid JSON representations: null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]} - hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it's fine).
The only difference between json & jsonb is their storage:
jsonis stored in its plain text format, whilejsonbis stored in some binary representation
There are 3 major consequences of this:
jsonbusually takes more disk space to store thanjson(sometimes not)jsonbtakes more time to build from its input representation thanjsonjsonoperations take significantly more time thanjsonb(& parsing also needs to be done each time you do some operation at ajsontyped value)
When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:
- If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use
json. - If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use
jsonb.
2 of 9
194
Peeyush:
The short answer is:
- If you are doing a lot of JSON manipulation inside PostgreSQL, such as sorting, slicing, splicing, etc., you should use JSONB for speed reasons.
- If you need indexed lookups for arbitrary key searches on JSON, then you should use JSONB.
- If you are doing neither of the above, you should probably use JSON.
- If you need to preserve key ordering, whitespace, and duplicate keys, you should use JSON.
For a longer answer, you'll need to wait for me to do a full "HowTo" writeup closer to the 9.4 release.
PostgreSQL
postgresql.org › docs › current › datatype-json.html
PostgreSQL: Documentation: 18: 8.14. JSON Types
August 13, 2026 - Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types · The input/output syntax for the JSON data types is as specified in RFC 7159. The following are all valid json (or jsonb) expressions:
Address: 178 Howard Place, Gulf, Washington, 702
JSON & JSONB Types in PostgreSQL | Part 1
23:19
Even JSONB In Postgres Needs Schemas | POSETTE 2024 - YouTube
04:58
Faster queries with index on JSONB columns in Postgres - YouTube
21:31
All about using JSON data in Postgres - YouTube
00:41
#12 PostgreSQL- JSON, Create Table and Load Sample Data. #Shorts ...
05:05
How to store and query JSON data in Postgres - YouTube
Logto
blog.logto.io › mastering-postgresql-jsonb
Mastering PostgreSQL JSONB type in one article · Logto blog
June 6, 2024 - For simple key-value pair queries, a B-tree index can also improve performance significantly. B-tree indexes are suitable for range queries and sorting operations. The following example shows how to create a B-tree index for the model key in the details ... PostgreSQL's JSONB type provides a powerful and flexible solution for handling JSON data.
DbSchema
dbschema.com › blog › postgresql › postgresql jsonb: operators, gin indexes, and query examples | dbschema
PostgreSQL JSONB: Operators, GIN Indexes, and Query Examples | DbSchema
September 10, 2026 - The examples below run on PostgreSQL 18 against one table. The identifiers stay in ordinary columns, and only the part that varies is a document: CREATE TABLE countries ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, cities JSONB );
GeeksforGeeks
geeksforgeeks.org › postgresql › what-is-jsonb-in-postgresql
What is JSONB in PostgreSQL? - GeeksforGeeks
July 23, 2025 - A new row is inserted with the name value 'Smartphone' and details containing the JSON data. After running this query, PostgreSQL will insert the product information into the table and return INSERT 0 1, confirming that one row has been added.
TigerData
tigerdata.com › learn › how-to-query-jsonb-in-postgresql
How to Query JSONB in PostgreSQL | Tiger Data
February 17, 2025 - In summary, JSONB is a powerful tool for handling JSON data within your PostgreSQL database. It allows the user to easily store, search, and manipulate complex data structures, making it a valuable asset for many applications.
DEV Community
dev.to › techprane › how-to-use-jsonb-in-postgresql-with-step-by-step-instructions-34k8
How to Use JSONB in PostgreSQL with Step-by-Step Instructions - DEV Community
October 30, 2024 - Here, the attributes column will store product details (e.g., size, color) in JSONB format, allowing for flexible attributes per product. Make sure the PostgreSQL user has the necessary admin permissions to create tables and perform CRUD operations.
Neon
neon.com › postgresql › json-functions › jsonb-operators
PostgreSQL JSONB Operators
The operator -> allows you to extract a field as a JSONB value from a JSON object by a key: ... Note that the key is surrounded by a single quote because the key in a JSON object is a text string. For example, the following query uses the operator -> to get the product names from the products table:
Aiven
aiven.io › developer › postgresql-jsonb-cheatsheet
PostgreSQL® JSONB Cheatsheet: Complete & Fast Lookup Guide
July 10, 2026 - A cheatsheet for the PostgreSQL® JSONB functions, providing a set of consistent examples of all the most common JSONB (and JSON) functions and operators.
Crunchy Data
crunchydata.com › developers › playground › basics-of-jsonb
Basics of JSON | Tutorials | Crunchy Data
Postgres has two JSON datatypes: JSONB and JSON. JSONB is an optimized binary version of JSON, which is slower to store, but is optimized for querying and processing. JSON is an exact copy of the data with limited query functionality.
Medium
mareks-082.medium.com › postgresql-jsonb-in-net-25fbcc7b64b2
PostgreSQL JSONB in .NET. PostgreSQL got some new JSON features… | by Marek Sirkovský | Medium
July 29, 2025 - One thing worth mentioning is that JSONB is a TOASTable type. That means that PostgreSQL can store large JSONB documents efficiently by compressing and storing them in a separate TOAST (The Oversized-Attribute Storage Technique) table.
Medium
medium.com › @suffyan.asad1 › an-introduction-to-working-with-json-data-in-postgresql-730aa889c5d3
An introduction to working with JSON data in PostgreSQL | by Suffyan Asad | Medium
September 4, 2023 - This article introduces the features provided by PostgreSQL to store and manipulate JSON data. It covers the json and jsonb data types for JSON columns, querying JSON data, and JSON indexes, with examples.
DB Vis
dbvis.com › thetable › json-vs-jsonb-in-postgresql-a-complete-comparison
JSON vs. JSONB in PostgreSQL: A Complete Comparison
October 22, 2025 - For all information, read our guide on jsonb. In detail, jsonb is based on an optimized format that supports many new operations. For example, extracting the value associated with an object key becomes lightning fast.
ScaleGrid
scalegrid.io › blog › using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql
JSONB PostgreSQL: How To Store & Index JSON Data
November 8, 2024 - Until then, the workaround might be using more compact names (unix-style) instead of more descriptive ones. For example, if you’re storing millions of instances of a particular key, it would be better storage-wise to name it “pb” instead of “publisherName”. The most efficient way to leverage JSONB in PostgreSQL is to combine columns and JSONB.
Beekeeper Studio
beekeeperstudio.io › blog › jsonb-functions-in-postgresql
Master JSONB Functions in PostgreSQL: A Practical Guide | Beekeeper Studio
August 24, 2025 - The -> operator returns data in JSON format, while ->> returns text. SELECT data->'name' AS name, data->>'email' AS email FROM users; ... name | email ---------+-------------------- "John Doe" | john@example.com "Jane Doe" | jane@example.com
Silota
silota.com › sql recipes › database compatibility › querying json (jsonb) data types in postgresql
Querying JSON (JSONB) data types in PostgreSQL · Advanced SQL · SILOTA
PostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. Because the -> operator returns an object, you can chain it to inspect deep into a JSON document. For example,
Architecture Weekly
architecture-weekly.com › architecture weekly › postgresql jsonb - powerful storage for semi-structured data
PostgreSQL JSONB - Powerful Storage for Semi-Structured Data
April 21, 2025 - With JSONB, you can implement this in PostgreSQL without forcing every customer profile into the same rigid structure: CREATE TABLE customers ( id TEXT PRIMARY KEY, email TEXT UNIQUE NOT NULL, customer_data JSONB NOT NULL ); This allows storing ...
DB Vis
dbvis.com › thetable › everything-you-need-to-know-about-the-postgres-jsonb-data-type
Everything You Need to Know About the Postgres JSONB Data Type
October 8, 2024 - When converting textual JSON input ... of the destination PostgreSQL data types. For example, jsonb will reject numbers that fall outside the range of the numeric data type....
PostgreSQL
postgresql.org › docs › 9.5 › functions-json.html
PostgreSQL: Documentation: 9.5: JSON Functions and Operators
February 11, 2021 - This is a non-issue if the input is type jsonb, because the conversion was already done; but for json input, this may result in throwing an error, as noted in Section 8.14. Note: While the examples for the functions json_populate_record, json_populate_recordset, json_to_record and json_to_recordset use constants, the typical use would be to reference a table in the FROM clause and use one of its json or jsonb columns as an argument to the function.
Hevo
hevodata.com › home › learn › database management system
How to Query JSONB Array of Objects in PostgreSQL
March 24, 2026 - Note that this begins at 1. However, ... refers to a keyword. Example: In this example, we will expand the JSONB document to an array format for the student having id =2....