Published at Jan 29, 2025

How to Query JSON Metadata in PostgreSQL

Written by Warda Bibi

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:

• Array : [15.2, 16.5, 15.8]

• Object: { "device_id": "device_789",  "timestamp": "2024-12-29T10:30:00Z", "battery": 85.4}

• Number: 100, 9.99, etc.

• Boolean: true or false

• String: “Joe”

• Null: null

Key point about JSON arrays

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.

For example, a JSON array can look like this:

[1, "hello", true, null, {"key": "value"}, [2, 3]] Copy

JSON structure

A JSON might include an arbitrary set of properties, such as:

{   "device_id": "device_789",   "status": "active",   "battery": 85.4,   "location": {"city": "New York", "zip": "10001"},   "alerts": ["low_battery", "maintenance_due"],   "metadata": {"model": "device_X", "os_version": "v3.4"} } Copy

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 ); Copy

Let’s insert a record:

INSERT INTO weather_stations (station_data) VALUES (     '{         "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"]          }     }'::json ); Copy

Note: The ::json casts the string into JSON format for storage.

 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"]          +     |          }                                                        +     | } Copy

Querying a JSON Column

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; Copy

The output of the above query is:

  arrow   | arrow_text  ----------+------------  "active" | activeCopy

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; Copy

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+  }                     | } Copy 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; Copy

Output:

             arrow               --------------------------------  ["City A", "City B", "City C"] Copy

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; CopyError:

ERROR:  operator does not exist: text ->> unknown LINE 1: SELECT  station_data->>'location'->>'nearby_cities' AS Arrow...Copy

                                         ^ HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. CopyThis 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' ; CopyThis 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"]          +     |         }                                                         +     | } Copy

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; Copy

Output:

 third_nearby_city  -------------------  "City C"Copy

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; Copy 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" Copy 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.

SELECT     TO_JSON(510),     TO_JSON(13.52),     TO_JSON('Command Prompt'::text),     TO_JSON(True); Copy

Output: to_json | to_json |     to_json      | to_json  ---------+---------+------------------+---------  510     | 13.52   | "Command Prompt" | true Copy

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.

SELECT to_json(35), pg_typeof(to_json(35)); Copy

Output:

 to_json | pg_typeof  ---------+-----------  35      | json Copy

Array conversion

to_json can convert PostgreSQL arrays (including multidimensional) into JSON arrays.

SELECT to_json(ARRAY[1, 2, 3]); CopyOutput:

 to_json  ---------  [1,2,3] Copy

Composite types conversion

to_json can also convert composite type into a JSON object, as shown below.

SELECT to_json(ROW(1, 'sensor', true));

Output:

             to_json               ----------------------------------  {"f1":1,"f2":"sensor","f3":true}Copy

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[]); Copy

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]]Copy

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.

SELECT json_build_array(1, 2, 'three', true, null); Copy Output:

      json_build_array        -----------------------------  [1, 2, "three", true, null]Copy

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); Copy

Output:

                      json_build_object                        --------------------------------------------------------------  {"name" : "sesnsorA", "temperature" : "100", "active" : true}Copy

This query creates a JSON object with three key-value pairs:

•  "name": "sensorA", 

•  "temperature": "100", 

•   "active": "true"

json_object

json_object is a PostgreSQL function that creates a JSON object from a set of key-value pairs. 

Function signature

json_object has two valid function signatures:

• Two-array approach:      json_object(keys TEXT[], values TEXT[]) 

• Single-array approach:     json_object(keys_values TEXT[]) 

Two-array approach

It takes two text arrays as input:

• One array represents keys. 

• The other array represents values. 

Each element in the first array becomes a key, and the corresponding element in the second array becomes its value.

Both arrays must have the same number of elements, as each key is paired with the corresponding value to construct the JSON object.

SELECT json_object(array['device_name', 'sensor_id', 'status', 'battery_level'],array['Sensor_1', '12345', 'active', '85']); Copy

Output:                              json_object                                             ---------------------------------------------------------------------------  {"device_name" : "Sensor_1", "sensor_id" : "12345", "status" : "active",   "battery_level" : "85"}Copy

This query creates a JSON object where:

• "device_name" maps to "Sensor_1."

• "sensor_id" maps to "12345."

• "status" maps to "active." 

• "battery_level" maps to "85."

Single-array approach

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.

SELECT json_object(array['device_name', 'Temp_Sensor', 'sensor_id', '98765', 'status', 'inactive', 'battery_level', '60']);

Output:

json_object                                               ------------------------------------------------------------------------------  {"device_name" : "Temp_Sensor", "sensor_id" : "98765", "status" : "inactive", "battery_level" : "60"}Copy

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 | byteaCopy

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 ); CopyThe result of executing this query on PostgreSQL 17+ will be as follows:

# |                   json_serialize -------------------------------------------------------- 1 | \x7b226964223a20312c202264617461223a202274657374227d Copy

This representation is space-efficient and suitable for storage or transmission in systems that prefer binary data.

Serializing JSON to text

To serialize a JSON object and return it as plain text:

SELECT json_serialize('{"name": "Alice", "age": 30}'); Copy

The result of executing this query on PostgreSQL 17 will be as follows.

# |       json_serialize -------------------------------- 1 | {"name": "Alice", "age": 30}Copy

Comparison with the json() function

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 Copy

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 Copy

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