HOWTO use JSON functionality in PostgreSQL

In a previous post, I talked about the excitement that surrounds NoSQL support in PostgreSQL. Today, I will dive a little deeper into the technical details of native JSON support that makes NoSQL possible.

Below, I explain how to use some basic functions and operators.

Creating a table with JSON column

So this is fairly simple. You can declare a JSON column just like a column of any other data type. Below, I create a table ‘sales’ with 2 columns, ‘id’ and ‘sale’, with the latter being a JSON.

json_sample=# CREATE TABLE sales (id INT, sale JSON);
CREATE TABLE

Inserting JSON data

Insertion of data is pretty close to that of any other data type as well, except that you have to make sure the data is in a valid JSON format. Below, I am inserting 4 records into the table, each with a JSON containing nodes for ‘customer_name’ and a nested JSON for ‘items’ containing a ‘description’ and purchased ‘quantity’.

John bought 4 cartons of milk:

json_sample=# INSERT INTO sales 
              VALUES (1,'{ "customer_name": "John", 
                           "items": { "description": "milk", "quantity": 4 } }');
INSERT 0 1

Susan bought 2 loaves of bread:

json_sample=# INSERT INTO sales 
              VALUES (2,'{ "customer_name": "Susan", 
                           "items": { "description": "bread", "quantity": 2 } }');
INSERT 0 1

Mark bought a dozen bananas:

json_sample=# INSERT INTO sales 
              VALUES (3,'{ "customer_name": "Mark", 
                           "items": { "description": "bananas", "quantity": 12 } }');
INSERT 0 1

Jane bought a pack of cereal:

json_sample=# INSERT INTO sales 
              VALUES (4,'{ "customer_name": "Jane", 
                           "items": { "description": "cereal", "quantity": 1 } }');
INSERT 0 1

Retrieving JSON data

Like insertion, retrieving the JSON formatted data is fairly straightforward as well. Below, I am retrieving the data I inserted in the previous section.

json_sample=# SELECT * FROM sales;
id |                                      sale
----+------------------------------------------------------------------------------------
1 | { "customer_name": "John", "items": { "description": "milk", "quantity": 4 } }
2 | { "customer_name": "Susan", "items": { "description": "bread", "quantity": 2 } }
3 | { "customer_name": "Mark", "items": { "description": "bananas", "quantity": 12 } }
4 | { "customer_name": "Jane", "items": { "description": "cereal", "quantity": 1 }

Retrieving JSONs – The ‘->’ and ‘->>’ operators

Now comes the real fun part! PostgreSQL provides native operators to retrieve individual nodes of the JSON object … very powerful indeed. In this section, I discuss the ‘->’ operator, which return a JSON object and the ‘->>’ operator, which returns TEXT.

Retrieving as a JSON:

json_sample=# SELECT sale->'customer_name' AS name FROM sales;
name
---------
"John"
"Susan"
"Mark"
"Jane"
(4 rows)

Retrieving as TEXT:

json_sample=# SELECT sale->>'customer_name' AS name FROM sales;
name
-------
John
Susan
Mark
Jane
(4 rows)

Chaining the ‘->’ and ‘->>’ operators

Since ‘->’ returns a JSON object, you can use it to return a nested object within the data and chain it with the operator ‘->>’ to retrieve a specific node.

json_sample=# SELECT id, sale->'items'->>'quantity' AS quantity FROM sales;
id | quantity
----+----------
1 | 4
2 | 2
3 | 12
4 | 1
(4 rows)

Using JSONs in extract criteria for queries

The operators discussed in the previous section can be used in the WHERE clause of a query to specify an extract criteria. A few examples, using the same data set, below.

Searching for a specific description of an item within a sale:

json_sample=# SELECT * FROM sales WHERE sale->'items'->>'description' = 'milk';
id |                                     sale
----+--------------------------------------------------------------------------------
1 | { "customer_name": "John", "items": { "description": "milk", "quantity": 4 } }
(1 row)

Searching for a specific quantity as TEXT:

json_sample=# SELECT * FROM sales WHERE sale->'items'->>'quantity' = 12::TEXT;
id |                                       sale
----+------------------------------------------------------------------------------------
3 | { "customer_name": "Mark", "items": { "description": "bananas", "quantity": 12 } }
(1 row)

Searching for a specific quantity as INTEGER:

json_sample=# SELECT * FROM sales WHERE CAST(sale->'items'->>'quantity' AS integer)  = 2;
 id |                                       sale                                       
----+----------------------------------------------------------------------------------
  2 | { "customer_name": "Susan", "items": { "description": "bread", "quantity": 2 } }
(1 row)

Using JSON nodes in aggregate functions

Once you understand how to retrieve individual nodes of a JSON object, you can easily use the retrieved values in aggregate functions as well.

json_sample=# SELECT SUM(CAST(sale->'items'->>'quantity' AS integer)) AS total_quantity_sold FROM sales;
total_quantity_sold
---------------------
19
(1 row)

JSON functions in PostgreSQL

Let’s go through some functions that PostgreSQL provides for manipulating JSON objects.

json_each

This function expands the outermost JSON object into a set of key/value pairs. Notice that the nested JSONs are not expanded.

json_sample=# SELECT json_each(sale) FROM sales;
json_each
--------------------------------------------------------------
(customer_name,"""John""")
(items,"{ ""description"": ""milk"", ""quantity"": 4 }")
(customer_name,"""Susan""")
(items,"{ ""description"": ""bread"", ""quantity"": 2 }")
(customer_name,"""Mark""")
(items,"{ ""description"": ""bananas"", ""quantity"": 12 }")
(customer_name,"""Jane""")
(items,"{ ""description"": ""cereal"", ""quantity"": 1 }")
(8 rows)

json_object_keys

Returns set of keys in the outermost JSON object. Again, notice that the nested keys are not displayed.

json_sample=# SELECT json_object_keys(sale) FROM sales;
json_object_keys
------------------
customer_name
items
customer_name
items
customer_name
items
customer_name
items
(8 rows)

json_typeof

Returns the type of the outermost JSON value as a text string. Possible types are ‘object’, ‘array’, ‘string’, ‘number’, ‘boolean’, and NULL.

json_sample=# SELECT json_typeof(sale->'items'), json_typeof(sale->'items'->'quantity') FROM sales;
json_typeof | json_typeof
-------------+-------------
object     | number
object     | number
object     | number
object     | number
(4 rows)

json_object

Builds a JSON object out of a text array. The function can be used in one of two ways:

(1) Array with exactly one dimension with an even number of members. In this case the elements are taken as alternating key/value pairs.

json_sample=# SELECT json_object('{key1, 6.4, key2, 9, key3, "value"}');
json_object
--------------------------------------------------
{"key1" : "6.4", "key2" : "9", "key3" : "value"}
(1 row)

(2) Array with two dimensions such that each inner array has exactly two elements. In this case, the inner array elements are taken as a key/value pair.

json_sample=# SELECT * FROM json_object('{{key1, 6.4}, {key2, 9}, {key3, "value"}}');
json_object
--------------------------------------------------
{"key1" : "6.4", "key2" : "9", "key3" : "value"}
(1 row)

to_json

Returns the value as a JSON object.

json_sample=# CREATE TABLE json_test (id INT, name TEXT);
CREATE TABLE
json_sample=# INSERT INTO json_test VALUES (1, 'Jack');
INSERT 0 1
json_sample=# INSERT INTO json_test VALUES (2, 'Tom');
INSERT 0 1
json_sample=# SELECT row_to_json(row(id, name)) FROM json_test;
row_to_json
----------------------
{"f1":1,"f2":"Jack"}
{"f1":2,"f2":"Tom"}
(2 rows)

 

PostgreSQL official documentation has a more comprehensive listing of the available JSON functions.

Have questions? Contact us NOW!

Join the discussion 3 Comments

  • Andrew Banks says:

    Thank you so much for this post! The only thing I don’t like is that you have to cast a numeric JSON node to integer to compare it to a literal number in SQL:

    SELECT * FROM sales WHERE CAST(sale->’items’->>’quantity’ AS integer) = 2;
    id | sale
    —-+———————————————————————————-
    2 | { “customer_name”: “Susan”, “items”: { “description”: “bread”, “quantity”: 2 } }

    Since sale->’items’->>quantity is already a number type, why must we cast it?

    Otherwise, all these capabilities are very exciting. I’m looking forward to using them when I can.

    • Umair Shahid says:

      Andrew, you are right, it is certainly less than the most convenient way to compare. For now, the ->> operator returns TEXT only, but I am sure the community will aggressively add features in future releases.

  • You may also use the `#>` operator instead of chaining `->` calls, as well as the `#>>` operator:

    SELECT * FROM sales WHERE sale#>>'{items,description}’ = ‘milk';

Leave a Reply


− 7 = 1

Fortune 500 company uses PostgreSQL to implement BI Download the case study