Notice that we used the type cast to convert the qty field into INTEGER type and compare it with two. WHERE CAST ( info -> 'items' -> 'qty' AS INTEGER) = 2 Code language: SQL (Structured Query Language) ( sql ) To find out who bought two products at a time, we use the following query: SELECT info -> 'customer' AS customer, WHERE info -> 'items' -> 'product' = 'Diaper' Code language: SQL (Structured Query Language) ( sql ) For example, to find out who bought Diaper, we use the following query: SELECT info -> 'customer' AS customer We can use the JSON operators in WHERE clause to filter the returning rows. And then info->'items'->'product' returns all products as text. ORDER BY product Code language: SQL (Structured Query Language) ( sql )įirst info -> 'items' returns items as JSON objects. For example, the following statement returns all products sold: SELECT info -> 'items' -> 'product' as product The following query uses the operator -> to get all customers in form of JSON: SELECT info -> 'customer' AS customerįROM orders Code language: SQL (Structured Query Language) ( sql )Īnd the following query uses operator -> to get all customers in form of text: SELECT info -> 'customer' AS customerīecause -> operator returns a JSON object, you can chain it with the operator -> to retrieve a specific node. The operator -> returns JSON object field by text.The operator -> returns JSON object field by key.

PostgreSQL provides two native operators -> and -> to help you query JSON data. PostgreSQL returns a result set in the form of JSON. To query JSON data, you use the SELECT statement, which is similar to querying other native data types: SELECT info FROM orders Code language: SQL (Structured Query Language) ( sql ) VALUES( '') Ĭode language: SQL (Structured Query Language) ( sql ) Querying JSON data The following INSERT statement inserts a new row into the orders table. To insert data into a JSON column, you have to ensure that data is in a valid JSON format.

The info column stores the data in the form of JSON.

The id column is the primary key column that identifies the order.

The orders table consists of two columns: ) Code language: SQL (Structured Query Language) ( sql ) Let’s get started by creating a new table for practicing with JSON data type. PostgreSQL supports native JSON data type since version 9.2. It provides many functions and operators for manipulating JSON data. Unlike other formats, JSON is human-readable text. The main usage of JSON is to transport data between a server and a web application. JSON is an open standard format that consists of key-value pairs. JSON stands for JavaScript Object Notation.

Summary: in this tutorial, you will learn how to how to work with PostgreSQL JSON data type and some useful operators and functions for handling JSON data.

Postgres update json field