PostgreSQL is a highly capable relational database management system known for its powerful features and performance. One of its standout capabilities is its ability to manage unstructured data efficiently. Unstructured data refers to information that doesn't fit neatly into fixed columns and data types. It can include complex structures with varying data types.
In this blog, we will explore the power of JSON in PostgreSQL and demonstrate how to store and query unstructured data efficiently in JSON.
JSON (JavaScript Object Notation)
JSON, short for JavaScript Object Notation, is a process of storing data in “key-value” pairs format. It is a lightweight, human-readable data interchange format that is easy for computers to parse and generate.
It is a flexible, schema-less format that handles hierarchical and dynamic data. It allows us to store an arbitrary number of properties with varying types, lengths, and structures without worrying about fitting into a fixed table format.
Data types in JSON
JSON is primarily built on two core data structures: objects and arrays. JSON supports the following data types:
A JSON array is an ordered collection of values enclosed in square brackets [ ]. The significant feature of JSON arrays is their ability to hold heterogeneous (mixed) data types within the same array. This means that elements in a JSON array can vary in type. They can be a mix of numbers, strings, booleans, null, objects, or even other arrays.
This is a typical JSON object with key-value pairs for properties like status, battery, location, and alerts. Each property can hold different data types, such as strings, numbers, booleans, arrays, or nested objects.
PostgreSQL Support for JSON
Postgres has native support for JSON starting from version 9.2 and onward releases. PostgreSQL provides two data types for working with JSON data:
JSON
JSONB
The difference between JSON and JSONB is that JSON stores data in raw text form exactly as provided, while JSONB converts it to a binary format before storing it. As a result, writing to JSON is faster, but querying is slower, whereas writing to JSONB is slower, but querying is much faster.
Creating a JSON Column
Let's create a table for storing weather station data, where each row represents a single station. The table will include an ID column (as a primary key) and a JSON column to store the station data.
CREATE TABLE weather_stations (
id SERIAL PRIMARY KEY,
station_data JSON
);
PostgreSQL has two native operators for retrieving a JSON column:
Arrow operator (->) - returns a JSON object field
Arrow-text operator (->>) - returns the JSON object field as plain text
The arrow operator -> is suitable for navigating nested structures.
Using the arrow-text operator, we can not query deeper into the structure because it’s no longer treated as an object.
Retrieving a Specific JSON Key
Let's try to understand ->, ->> operators better with the help of a simple example.
SELECT station_data-> 'status' As Arrow, station_data->> 'status' As Arrow_text FROM weather_stations;
The output of the above query is:
arrow | arrow_text
----------+------------
"active" | active
Here, status is a string so:
->’status’ should return an object field “active.”
->>’status’ should return it as plain text active (without quotations if the value is a string).
Similarly, if we want to query the field ‘gps_coordinates’, we can use both -> and ->> operators. The results will appear the same, but internally, -> returns the entire object, whereas ->> extracts it as plain text.
SELECT station_data-> 'location'-> 'gps_coordinates' As Arrow, station_data-> 'location'->> 'gps_coordinates' As Arrow_text FROM weather_stations;
This query will return the gps_coordinates object for -> and the corresponding plain text value for ->>.
arrow | arrow_text
-----------------------+-----------------------
{ +| { +
"latitude": 37.7749, +| "latitude": 37.7749, +
"Longitude":-122.4194+| "Longitude":-122.4194+
} | }
This means we cannot query deeper into ->> gps_coordinates because it returns a plain text value.
Retrieving a Nested JSON Key
Let’s try to query deeper inside ‘location’ using -> and ->>.
SELECT station_data->'location'->'nearby_cities' AS Arrow FROM weather_stations;
This will work correctly because -> retains the JSON object format, allowing further traversal. However, the query:
SELECT station_data->>'location'->>'nearby_cities' AS Arrow_text FROM weather_stations;
Error:
ERROR: operator does not exist: text ->> unknown
LINE 1: SELECT station_data->>'location'->>'nearby_cities' AS Arrow...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
This query results in an error because ->> extracts location as plain text and PostgreSQL cannot navigate further into plain text to access nearby_cities.
Filtering Rows Based on a Specific JSON Key Value
We can also use the -> and ->> operators in the WHERE clause. Let’s say we want to find all the active stations we can query.
SELECT * FROM weather_stations WHERE station_data->> 'status'='active' ;
This query will return all rows where the status key in the station_data JSON column has the value 'active’.
id | station_data
----+--------------------------------------------------------------------
1 | { +
| "station_name": "Weather Station Alpha", +
| "status": "active", +
| "location": { +
| "gps_coordinates": { +
| "latitude": 37.7749, +
| "Longitude":-122.4194 +
| }, +
| "nearby_cities": ["City A", "City B", "City C"], +
| "climatic_zones": ["Temperate", "Coastal"] +
| } +
| }
Querying arrays inside a JSON column
Let’s understand querying arrays inside a JSON column. Similar to querying any other key, we can access an array using the arrow operator (->). To query a specific position within an array, we use the arrow operator followed by the index of the desired element. If we want the result as plain text (e.g., retrieving the nearby_city as a string), we can use the ->> operator instead.
For example, to find the third nearby city for a station with id = 1, we can write:
SELECT station_data->'location'->'nearby_cities'->2 AS third_nearby_city FROM weather_stations WHERE id = 1;
Output:
third_nearby_city
-------------------
"City C"
Note: Array indexing in PostgreSQL starts at 0, so the third city corresponds to index 2.
#> (Path Operator)
The #> operator is used to access elements in a JSON structure using a path. The path is specified as an array of keys and indices, allowing you to drill down into nested JSON objects or arrays.
SELECT station_data#>'{location,climatic_zones,1}' AS climatic_zone FROM weather_stations WHERE id = 1;
Here, the top-level key is ‘location,’ and within that, there's another key ‘climatic_zones’ containing the array ["Temperate," "Coastal"] where the value at index 1 is Coastal. The path '{location,climatic_zones,1}' means the following:
Start at the top-level key ‘location,’ which is a nested object.
Move into the ‘climatic_zones’ array.
Finally, extract the value of the index ‘1’.
This will give us the value "Coastal" at the index level.
climatic_zone
---------------
"Coastal"
We also have a #>> operator. The difference between#> and #>> is exactly like the difference between -> and ->>. While#> returns a JSON object or array at a specified path, #>> returns the value as plain text. Use #>> for a text result instead of a JSON structure.
JSON Functions
to_ json
to_json() is a built-in JSON function that accepts any valid SQL value and converts it into JSON format. It can handle arrays, objects, and scalar values. The reason for converting a scalar value to a JSON is to ensure data consistency, especially in systems that expect JSON as a standard format, such as APIs or NoSQL databases.
Scalar conversion
Scalars ( text, numbers, booleans) are converted into JSON, as shown below.
To verify that to_json(value) returns a JSON value, we can use the PostgreSQL system catalog function pg_typeof() to check the data type of the result.
ROW is a PostgreSQL construct that creates an anonymous composite type (a temporary record or row) with the specified values.
f1, f2, and f3 are the default field names assigned to the values in an anonymous composite type.
array_to_json
array_to_json converts PostgreSQL arrays into a JSON representation. It supports multidimensional arrays, producing nested JSON arrays:
SELECT array_to_json('{{1,5},{99,100}}'::int[]);
Here, {{1,5},{99,100}} is a 2D integer array consisting of two inner arrays: {1, 5} and {99, 100}.
array_to_json
------------------
[[1,5],[99,100]]
This query converts each inner array into a JSON array.
The first inner array [1, 5] becomes a JSON array [1, 5].
The second inner array [99, 100] becomes a JSON array [99, 100].
The outer array [ ] contains these two nested JSON arrays, resulting in the final JSON output:
[[1, 5], [99, 100]]
Difference between to_json and array_to_json
The difference between to_json and array_to_json in array conversion is that to_json can convert any PostgreSQL value (including arrays) into JSON. In contrast, array_to_json is specifically designed for converting PostgreSQL arrays into JSON arrays, and it includes an optional pretty_bool parameter for formatting the output.
json_build_array
json_build_array is a PostgreSQL function that constructs a JSON array from a list of arguments. It supports elements of mixed types ( numbers, strings, booleans, etc.). The output is a JSON type, which is stored as text.
The output [1, 2, "three," true, null] is a JSON array that contains mixed elements: two integers (1, 2), a string ("three"), a boolean (true), and a null value (null). JSON arrays can hold multiple types of values in a specific order without requiring key-value pairs.
json_build_object
json_build_object is a PostgreSQL function that allows the creation of a JSON object by specifying alternating key-value pairs. Each key is associated with a value, and the function constructs a JSON object where the keys and values are stored as pairs.
SELECT json_build_object('name', 'sesnsorA', 'temperature', ‘100’, 'active', true);
Alternatively, we can pass a single text array containing both keys and values. In this case, alternate elements in the array are treated as keys and values, respectively.
The array is processed in pairs, and each pair is mapped as key value in the resulting JSON object. The first element ('device_name') becomes the key, and the next element ('Temp_Sensor') becomes its value. This pattern continues for the remaining pairs.
json_serialize
The json_serialize() function in PostgreSQL 17 converts a JSON expression into text or binary format. This function is particularly useful when we need to control the output format of JSON data or prepare it for transmission or storage in specific formats.
Function signature
The json_serialize() function uses the following syntax.
json_serialize( expression -- Input JSON expression
[ FORMAT JSON [ ENCODING UTF8 ]] -- Optional input format specification
[ RETURNING data_type -- Optional return type specification
[ FORMAT JSON [ ENCODING UTF8 ] ] ] -- Optional output format specification
) → text | bytea
Parameters:
expression: Inputs JSON value or expression to serialize
FORMAT JSON: Explicitly specifies JSON format for input (optional)
ENCODING UTF8: Specifies UTF8 encoding for input/output (optional)
RETURNING data_type: Specifies the desired output type (optional, defaults to text)
Note: Bytea is a data type used to store binary strings, which are sequences of bytes. When bytea data is displayed, it is often shown in a hexadecimal format, prefixed with \x.
For example,
\x7b20226122203a2031207d20
is the hexadecimal representation of the JSON string { "a" : 1 }.
Example usage
Serializing JSON to binary (bytea)
To convert a JSON object into a compact binary format:
SELECT json_serialize(
'{"id": 1, "data": "test"}'
RETURNING bytea
);
The result of executing this query on PostgreSQL 17+ will be as follows:
While both json_serialize() and json() are used with JSON data, their purposes differ.
json() converts text or binary data into JSON values.
json_serialize() converts JSON values into text or binary format.
json() emphasizes input validation, such as ensuring unique keys.
json_serialize() focuses on controlling the output format.
Think of these functions as complementary tools for handling JSON data
json() for input conversion
SELECT json('{"name": "sensorA"}'); -- Converts text to JSON
This query converts a text string '{"name": "sensorA"}' representing JSON into an actual JSON object in PostgreSQL.The output will be the JSON object {"name": "sensorA"}, stored in JSON format, allowing it to be used in JSON-specific operations in PostgreSQL.
json_serialize() for output conversion
SELECT json_serialize('{"name": "sensorA"}'::json); -- Converts JSON to text
This query converts a JSON object back into a text representation of the JSON. This means we can no longer apply JSON-specific operations on the resulting string, as it is now just a regular string in PostgreSQL.
Conclusion
In summary, JSON is a powerful tool for handling JSON data within your PostgreSQL database. It allows the user to easily store, search, and manipulate complex data structures, making it a valuable asset for many applications. With its flexibility and powerful features, JSON can significantly enhance the capabilities of the database.
TimescaleDB, an open-source extension that expands PostgreSQL with robust optimizations for time series, real-time analytics, events, and vector data, supports JSON and JSONB for semi-structured data. Also, check out this article to learn more about handling complex JSONB columns for real-time aggregation.
And since Timescale is PostgreSQL under the hood, you can benefit from PostgreSQL 17’s enhanced JSON support in all our Timescale Cloud services. Create a Timescale account and try it for free for 30 days (no credit card required).
Scale PostgreSQL via Partitioning: A Dev’s Intro to Hypertables
Boosting Postgres INSERT Performance by 2x With UNNEST