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 JSON data is fairly straightforward as well … as long as you want the original JSON formatted string and not particular nodes of the object. 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

———————

(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

(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.