PostgreSQL JSON

Hi JSON

My friend, JSON, is very popular these days. We will explore how to query JSON objects in this blog post.

Let’s create a table and populate it.

CREATE TABLE purchases (
   ID serial NOT NULL PRIMARY KEY,
   purchase json NOT NULL
);
INSERT INTO purchases (purchase)
VALUES
   (
      '{ "customer": "Steve", "items": {"product": "Beer","qty": 50}}'
   ),
   (
      '{ "customer": "Karen", "items": {"product": "Lotion","qty": 5}}'
   ),
   (
      '{ "customer": "Kyle", "items": {"product": "Monster Energy","qty": 9}}'
   );

Notice that we made a JSON type column called purchase and inserted JSON objects into the purchases table.

Let’s explore the different ways to query JSON objects.

SELECT purchase FROM purchases;
purchase                                
--------------------------------------------------------------------
 { "customer": "Steve", "items": {"product": "Beer","qty": 50}}
 { "customer": "Karen", "items": {"product": "Lotion","qty": 5}}
 { "customer": "Kyle", "items": {"product": "Monster Energy","qty": 9}}
(3 rows)

This gives us the JSON objects in the purchase column for each row.

SELECT purchase -> 'customer' AS customer FROM purchases;
 customer 
----------
 "Steve"
 "Karen"
 "Kyle"
(3 rows)

The -> operator returns all customers as JSON.

SELECT purchase ->> 'customer' AS customer FROM purchases;
 customer 
----------
 Steve
 Karen
 Kyle
(3 rows)

The ->> operator returns all customers as text.

SELECT purchase -> 'items' ->> 'product' AS product FROM purchases ORDER BY product;
    product     
----------------
 Beer
 Lotion
 Monster Energy
(3 rows)

You can chain -> and ->> together to get this. First the -> returns items as a JSON object, then ->> returns the items as text.

SELECT purchase ->> 'customer' AS customer                                         FROM purchases                                                                              WHERE purchase -> 'items' ->> 'product' = 'Monster Energy';
 customer 
----------
 Kyle
(1 row)

You can also use -> and ->> in the WHERE clause to select for specific values.

Thanks for reading!