Introduction to JSON support in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

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)
  1. 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)
  1. 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.