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