February 11, 2021 - Note: There are parallel variants of these operators for both the json and jsonb types. The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text.

JSONB operators work the same on any PostgreSQL deployment. Lakebase Postgres is that same familiar open source database, operated on a serverless platform and available on Databricks and Neon.

September 10, 2026 - PostgreSQL answers that with a few operators on the jsonb type: -> and ->> read a field, #>> follows a path, @> filters on a value, jsonb_array_elements turns an embedded array into rows, and a GIN index keeps the filter fast on a large table.

Hello, I'm using Postgresql and JSONB column. I would like to write a query using ORM that will generate such SQL query: SELECT id,name,details FROM Device WHERE details::text LIKE '�%'; where details is of JSONB type and it is converted to text and then like operator is used.

February 17, 2025 - PostgreSQL includes two native operators: arrow operator (->) and arrow-text operator (->>) to query JSONB documents.

June 17, 2025 - When I started working with JSONB I was confused by the variety of operators @>, ->, ->> and multiple ways to query the same fields. And even more puzzling was figuring out how indexing works, and why one query would use the index while another (querying the same field!), won’t. In this post I have put together what I learned so far. While there is much more to discover , I hope this should serve as a gentle , practical introduction to querying jsonB in postgres.

See also Section 9.20 for the aggregate function json_agg which aggregates record values as JSON, the aggregate function json_object_agg which aggregates pairs of values into a JSON object, and their jsonb equivalents, jsonb_agg and jsonb_object_agg. Some further operators also exist only for jsonb, as shown in Table 9.45.

The comparison operators follow the ordering rules for B-tree operations outlined in Section 8.14.4. See also Section 9.21 for the aggregate function json_agg which aggregates record values as JSON, the aggregate function json_object_agg which aggregates pairs of values into a JSON object, and their jsonb equivalents, jsonb_agg and jsonb_object_agg.

January 13, 2025 - CREATE INDEX indexname ON tablename USING GIN (jsonb_data jsonb_ops); CREATE INDEX indexname ON tablename USING GIN (jsonb_data jsonb_path_ops); If no operator class is specified, PostgreSQL defaults to jsonb_ops.

August 24, 2025 - For deeper JSON structures, chain the operators. The ? operator checks for key existence first: SELECT data->>'name' AS name, data->'address'->>'city' AS city FROM users WHERE data ? 'address'; ... PostgreSQL includes a suite of JSONB functions beyond simple value extraction.

November 8, 2024 - That doesn’t work since the ‘->’ operator returns a JSONB type. So we need to use something like this: demo=# select * from books where (data->'criticrating')::int4 > 4; If you’re using a version before PostgreSQL 11, it gets ugly.

August 13, 2026 - Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules. There are also assorted JSON-specific functions and operators available for data stored in these data types; see Section 9.16. PostgreSQL offers two types for storing JSON data: json and jsonb.

DROP DATABASE IF EXISTS books_db; CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0; DROP TABLE IF EXISTS books; CREATE TABLE books ( id SERIAL PRIMARY KEY, client TEXT NOT NULL, data JSONb NOT NULL ); INSERT INTO books(client, data) values ( 'Joe', '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }' ),( 'Jenny', '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }' ),( 'Jenny', '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }' ); ... The -> operator returns the original JSON type (which might be an object), whereas ->> returns text.

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.

July 23, 2025 - This query uses the @> operator to check if the details JSONB column contains a specific value within its nested array. It filters for products that have "USB-C" listed under the "ports" in the "features" object.

October 8, 2024 - PostgreSQL supports GIN indexes for efficient searching of keys or key/value pairs within jsonb documents. In particular, it provides two GIN operator classes, offering different trade-offs between performance and flexibility.

August 19, 2026 - A working reference for Postgres JSONB operators and functions: arrow operators, containment, JSONPath, jsonb_set, and which ones an index can use.

The comparison operators follow the ordering rules for B-tree operations outlined in Section 8.14.4. See also Section 9.21 for the aggregate function json_agg which aggregates record values as JSON, the aggregate function json_object_agg which aggregates pairs of values into a JSON object, and their jsonb equivalents, jsonb_agg and jsonb_object_agg.

2 weeks ago - For a detailed description and examples about containment and existence, see jsonb Containment and Existence in PostgreSQL’s documentation. ... This operator checks if a string exists as a top-level array element or object key within a jsonb value.

Note: There are parallel variants of these operators for both the json and jsonb types. The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text.