Share this page: Follow me to be notified of new articles:

Introduction to JSON support in PostgreSQL

Introduction

Since the version 9.2, PostgreSQL supports the data type JSON.
You can manipulate JSON in PostgreSQL with several functions and operators. Here is a simple introduction to it.

The JSON data type

You can create a column with the data type JSON in any table with PostgreSQL >= 9.2.

For the following examples, we will create a table with a JSON column and add stock quotes to it:
-- Creating a table with a JSON column
CREATE TEMP TABLE json_example (
   id serial,
   ticker text,
   history json
);

-- Adding the stock history of YAHOO
INSERT INTO json_example (ticker, history) VALUES ('YHOO', '{"query":{"count":6,"created":"2014-08-04T16:37:39Z","lang":"en-US","results":{"quote":[
{"Symbol":"YHOO","Date":"2014-08-01","Open":"35.69","High":"36.08","Low":"35.31","Close":"35.62","Volume":"14573000","Adj_Close":"35.62"}
,{"Symbol":"YHOO","Date":"2014-07-31","Open":"36.26","High":"36.49","Low":"35.68","Close":"35.81","Volume":"17925700","Adj_Close":"35.81"}
,{"Symbol":"YHOO","Date":"2014-07-30","Open":"35.94","High":"36.99","Low":"35.80","Close":"36.60","Volume":"29848900","Adj_Close":"36.60"}
,{"Symbol":"YHOO","Date":"2014-07-29","Open":"35.91","High":"36.16","Low":"35.67","Close":"35.68","Volume":"11570900","Adj_Close":"35.68"}
,{"Symbol":"YHOO","Date":"2014-07-28","Open":"36.23","High":"36.23","Low":"35.51","Close":"35.90","Volume":"14607200","Adj_Close":"35.90"}
,{"Symbol":"YHOO","Date":"2014-07-25","Open":"36.00","High":"36.33","Low":"35.75","Close":"36.12","Volume":"20123200","Adj_Close":"36.12"}
]}}}');

JSON Operators

You can easily grab your full JSON with a basic query:
gab@gab # SELECT history FROM json_example;
                                                                  history                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------
 {"query":{"count":6,"created":"2014-08-04T16:37:39Z","lang":"en-US","results":{"quote":[                                                  +
 {"Symbol":"YHOO","Date":"2014-08-01","Open":"35.69","High":"36.08","Low":"35.31","Close":"35.62","Volume":"14573000","Adj_Close":"35.62"} +
 ,{"Symbol":"YHOO","Date":"2014-07-31","Open":"36.26","High":"36.49","Low":"35.68","Close":"35.81","Volume":"17925700","Adj_Close":"35.81"}+
 ,{"Symbol":"YHOO","Date":"2014-07-30","Open":"35.94","High":"36.99","Low":"35.80","Close":"36.60","Volume":"29848900","Adj_Close":"36.60"}+
 ,{"Symbol":"YHOO","Date":"2014-07-29","Open":"35.91","High":"36.16","Low":"35.67","Close":"35.68","Volume":"11570900","Adj_Close":"35.68"}+
 ,{"Symbol":"YHOO","Date":"2014-07-28","Open":"36.23","High":"36.23","Low":"35.51","Close":"35.90","Volume":"14607200","Adj_Close":"35.90"}+
 ,{"Symbol":"YHOO","Date":"2014-07-25","Open":"36.00","High":"36.33","Low":"35.75","Close":"36.12","Volume":"20123200","Adj_Close":"36.12"}+
 ]}}}
(1 row)

However, it's not very useful because you could have very well stored the JSON in a "text" column and done the same thing.
PostgreSQL JSON Operators allows you to do much more than that.

Here are 3 examples of JSON manipulation:

1. You can grab part of a JSON object with its path:
gab@gab # SELECT history->'query'->'results'->'quote' as first_date FROM json_example;
                                                                 first_date                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 [                                                                                                                                         +
 {"Symbol":"YHOO","Date":"2014-08-01","Open":"35.69","High":"36.08","Low":"35.31","Close":"35.62","Volume":"14573000","Adj_Close":"35.62"} +
 ,{"Symbol":"YHOO","Date":"2014-07-31","Open":"36.26","High":"36.49","Low":"35.68","Close":"35.81","Volume":"17925700","Adj_Close":"35.81"}+
 ,{"Symbol":"YHOO","Date":"2014-07-30","Open":"35.94","High":"36.99","Low":"35.80","Close":"36.60","Volume":"29848900","Adj_Close":"36.60"}+
 ,{"Symbol":"YHOO","Date":"2014-07-29","Open":"35.91","High":"36.16","Low":"35.67","Close":"35.68","Volume":"11570900","Adj_Close":"35.68"}+
 ,{"Symbol":"YHOO","Date":"2014-07-28","Open":"36.23","High":"36.23","Low":"35.51","Close":"35.90","Volume":"14607200","Adj_Close":"35.90"}+
 ,{"Symbol":"YHOO","Date":"2014-07-25","Open":"36.00","High":"36.33","Low":"35.75","Close":"36.12","Volume":"20123200","Adj_Close":"36.12"}+
 ]
(1 row)

2. You can grab any element of a nested object:
-- First element from an object
gab@gab # SELECT history->'query'->'results'->'quote'->0 as first_element FROM json_example;
                                                               first_element                                                               
-------------------------------------------------------------------------------------------------------------------------------------------
 {"Symbol":"YHOO","Date":"2014-08-01","Open":"35.69","High":"36.08","Low":"35.31","Close":"35.62","Volume":"14573000","Adj_Close":"35.62"}
(1 row)

-- Third element from an object
gab@gab # SELECT history->'query'->'results'->'quote'->2 as third_element FROM json_example;
                                                               third_element                                                               
-------------------------------------------------------------------------------------------------------------------------------------------
 {"Symbol":"YHOO","Date":"2014-07-30","Open":"35.94","High":"36.99","Low":"35.80","Close":"36.60","Volume":"29848900","Adj_Close":"36.60"}
(1 row)

3. You can get a JSON object field as text:
gab@gab # SELECT history->'query'->'results'->'quote'->0->>'Date' as first_date FROM json_example;
 first_date 
------------
 2014-08-01
(1 row)

A complete list of JSON operators is available on the official documentation.

Functions to manipulate JSON

With json_array_elements(), we can expand a JSON array to a set of JSON elements. Our 6 days of quotes will become 6 rows:
gab@gab # SELECT json_array_elements(history->'query'->'results'->'quote') FROM json_example;
                                                            json_array_elements                                                            
-------------------------------------------------------------------------------------------------------------------------------------------
 {"Symbol":"YHOO","Date":"2014-08-01","Open":"35.69","High":"36.08","Low":"35.31","Close":"35.62","Volume":"14573000","Adj_Close":"35.62"}
 {"Symbol":"YHOO","Date":"2014-07-31","Open":"36.26","High":"36.49","Low":"35.68","Close":"35.81","Volume":"17925700","Adj_Close":"35.81"}
 {"Symbol":"YHOO","Date":"2014-07-30","Open":"35.94","High":"36.99","Low":"35.80","Close":"36.60","Volume":"29848900","Adj_Close":"36.60"}
 {"Symbol":"YHOO","Date":"2014-07-29","Open":"35.91","High":"36.16","Low":"35.67","Close":"35.68","Volume":"11570900","Adj_Close":"35.68"}
 {"Symbol":"YHOO","Date":"2014-07-28","Open":"36.23","High":"36.23","Low":"35.51","Close":"35.90","Volume":"14607200","Adj_Close":"35.90"}
 {"Symbol":"YHOO","Date":"2014-07-25","Open":"36.00","High":"36.33","Low":"35.75","Close":"36.12","Volume":"20123200","Adj_Close":"36.12"}
(6 rows)

It is now easy to grab our fields as columns:
gab@gab # WITH json_to_rows AS (
   SELECT json_array_elements(history->'query'->'results'->'quote') FROM json_example
)
SELECT json_array_elements->>'Date' as date,
       json_array_elements->>'Open' as open,
       json_array_elements->>'Close' as close
FROM json_to_rows;
    date    | open  | close 
------------+-------+-------
 2014-08-01 | 35.69 | 35.62
 2014-07-31 | 36.26 | 35.81
 2014-07-30 | 35.94 | 36.60
 2014-07-29 | 35.91 | 35.68
 2014-07-28 | 36.23 | 35.90
 2014-07-25 | 36.00 | 36.12
(6 rows)

In case of nested elements (like in our examples), you can count the number of elements with json_array_length():
gab@gab # SELECT json_array_length(history->'query'->'results'->'quote') FROM json_example;                                      
 json_array_length 
-------------------
                 6
(1 row)

Functions to convert to JSON

You can convert a PostgreSQL array to JSON with array_to_json():
gab@gab # SELECT ARRAY[1, 2, 3, 5];
   array   
-----------
 {1,2,3,5}
(1 row)

gab@gab # SELECT array_to_json(ARRAY[1, 2, 3, 5]);
 array_to_json 
---------------
 [1,2,3,5]
(1 row)
It's a convenient way to convert a PostgreSQL Array to a PHP Array (read more about that).

You can convert any element to JSON with to_json():
gab@gab # SELECT to_json('My name is Gabriel'::text);
       to_json        
----------------------
 "My name is Gabriel"
(1 row)

You can return any result from multiple rows as JSON with row_to_json():
gab@gab # CREATE TEMP TABLE foo (a text, b text, c text);
CREATE TABLE

gab@gab # INSERT INTO foo (a, b, c) VALUES ('one', 'two', 'three');
INSERT 0 1

-- Regular SELECT
gab@gab # SELECT a, b, c FROM foo;
  a  |  b  |   c   
-----+-----+-------
 one | two | three
(1 row)

-- SELECT with row_to_json();
gab@gab # SELECT row_to_json(row(a, b, c)) FROM foo;
             row_to_json              
--------------------------------------
 {"f1":"one","f2":"two","f3":"three"}
(1 row)

Read more

You can find a complete list of PostgreSQL JSON functions on the official documentation.
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.