PostgreSQL 9.5 and JSON

postresql_9.5.json.md

PostgreSQL and JSON

Install PostgreSQL 9.5

Install and configure the repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Install the package

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib  postgresql-server-dev-9.5 

Howto use JSON functionality in PostgreSQL

Creating a table with JSON column

You can declare a JSON column just like a column of any other data type.

CREATE TABLE sales (id INT, sale JSON);

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.

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

Retrieving JSON data

SELECT * FROM sales;

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

PostgreSQL provides native operators to retrieve individual nodes of the JSON object. The ‘->’ operator returns a JSON object and the ‘->>’ operator returns TEXT.

SELECT sale->'customer_name' AS name FROM sales;

SELECT sale->>'customer_name' AS name FROM sales;

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

‘->’ 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.

SELECT id, sale->'items'->>'quantity' AS quantity FROM sales;
SELECT sale->'customer_name' AS name FROM sales;

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.

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

 SELECT * FROM sales WHERE sale->'items'->>'description' = 'milk';

Searching for a specific quantity as TEXT:

SELECT * FROM sales WHERE sale->'items'->>'quantity' = 12::TEXT;

Searching for a specific quantity as INTEGER:

SELECT * FROM sales WHERE CAST(sale->'items'->>'quantity' AS integer)  = 2;

Using JSON nodes in aggregate functions

SELECT SUM(CAST(sale->'items'->>'quantity' AS integer)) AS total_quantity_sold FROM sales;

JSON functions in PostgreSQL

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.

SELECT json_each(sale) FROM sales;

json_object_keys

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

SELECT json_object_keys(sale) FROM sales;

json_typeof

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

SELECT json_typeof(sale->'items'), json_typeof(sale->'items'->'quantity') FROM sales;

json_object

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

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

  SELECT json_object('{key1, 6.4, key2, 9, key3, "value"}');

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

  SELECT * FROM json_object('{{key1, 6.4}, {key2, 9}, {key3, "value"}}');

to_json

Returns the value as a JSON object.

CREATE TABLE json_test (id INT, name TEXT);

Reources

• http://www.postgresql.org/docs/9.4/static/functions-json.html
• http://tecadmin.net/install-postgresql-server-on-ubuntu/
• http://stormatics.com/howto-use-json-functionality-in-postgresql/
• https://www.compose.io/articles/using-json-extensions-in-postgresql-from-python-2/