PostgreSQL offers a couple of native JSON operators to query the JSON data, such as the short arrow “->” and the long arrow “->>”. The short arrow “->” queries the JSON object by “key”, while the long arrow “->>” retrieves the JSON object by “text”. Using these operators, users can get a specific node of a JSON object.

This write-up presents a detailed guide on how to query JSON data in Postgres via suitable examples.

How to Query JSON Data in Postgres?

This section will show you how to query a JSON column in PostgreSQL using the JSON operators.

Example 1: Querying a JSON Column Using SELECT Statement

To retrieve data from a sample table named "product_order_details", use the SELECT query:

SELECT * FROM product_order_details
ORDER BY o_id ASC;

The SELECT statement can be used to query the data from the JSON column:

SELECT o_details FROM product_order_details;

The output signifies that the SELECT statement successfully retrieves the data from the JSON column.

Example 2: Querying a JSON Column Using Short Arrow “->”

In the following snippet, the “->” operator is used to get the JSON object field by “key”:

SELECT o_details -> 'cust_name' As cusatomer_names
FROM product_order_details;

The output proves that the “->” operator retrieves the data in JSON format.

Example 3: Querying a JSON Column Using Long Arrow “->>”

Replace the “->” operator with the “->>” operator to get the data in text format:

SELECT o_details ->> 'cust_name' As cusatomer_names
FROM product_order_details;

The “->>” operator retrieves the data in TEXT format.

Example 4: Querying a Specific Node From a JSON Object in Postgres

Use the short arrow “->” and the long arrow “->>” combinedly to query a specific node from a JSON object. The short arrow will return a JSON object while the long arrow will retrieve a specific node from that object.

For instance, in the following snippet, we use the “->>” operator with the “->” operator to get only the “pro_name” node from the JSON object:

SELECT o_details -> 'pro_description' ->> 'pro_name' As product_name
FROM product_order_details;

From the output, you can observe that the selected node has been accessed from the JSON object.

Example 5: Querying Filtered Data From the JSON Column

The WHERE clause is used with the JSON operators to filter the data based on a certain criterion. For instance, in the following snippet, the WHERE clause is used with the JSON operators to filter the result set of a query based on the given condition:

SELECT o_details -> 'cust_name' As customer_name
FROM product_order_details
WHERE o_details -> 'pro_description' ->> 'pro_name' = 'Laptop';

The above statement will show the customer’s name who bought the “laptop”:

The output shows that the WHERE clause filtered the JSON data based on the condition specified within it.

Conclusion

Postgres supports a couple of native JSON operators to query the data from a JSON column. These operators include a short arrow “->” and a long arrow “->>”. The short arrow queries the JSON object by “key”, while the long arrow retrieves the JSON object by “text”. Using these operators, users can get a specific node of a JSON object. This post illustrated various examples to show the usage of JSON operators in Postgres.