PostgreSQL JSON Extract Operators: -> And ->>

Summary: in this tutorial, you will learn how to use the operator -> and ->> to extract an element from a JSON array or a value of a key from a JSON object.

Extracting elements from JSON arrays

To extract an element of a JSON array as a JSONB value, you use the -> operator.

Here’s the syntax for using the -> operator:

json_array -> n

In this syntax, n locates the nth element in a JSON array. n can be positive or negative. If the n is negative, the operator -> returns the element from the end of the array.

Note that the first element has an index of zero and the last element has an index of -1.

If the nth element does not exist, the operator -> returns null. To extract an array element as a text string, you can use the ->> operator:

json_array ->> n

Extracting JSON array element examples

Let’s explore some examples of using the -> and ->> operators.

1) Setting up a sample table

First, create a new table called employees to store employee data:

CREATE TABLE employees(
   id SERIAL PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   phones JSONB NOT NULL
);

Second, insert some rows into the employees table:

INSERT INTO employees (name, phones)
VALUES
   ('John Doe', '["(408) 555-1111", "(408) 555-2222", "(408) 555-3333"]'),
   ('Jane Smith', '["(408) 666-1111", "(408) 666-2222", "(408) 666-3333"]')
RETURNING *;

Output:

id |    name    |                         phones
----+------------+--------------------------------------------------------
  1 | John Doe   | ["(408) 555-1111", "(408) 555-2222", "(408) 555-3333"]
  2 | Jane Smith | ["(408) 666-1111", "(408) 666-2222", "(408) 666-3333"]
(2 rows)

2) Extracting the first array element example

The following example uses the -> operator to retrieve the first phone number of an employee with the name John Doe:

SELECT
  name,
  phones -> 0 phone
FROM
  employees
WHERE
  name = 'John Doe';

Output:

name   |      phone
----------+------------------
 John Doe | "(408) 555-1111"
(1 row)

In this example, we use the -> operator with the index 0. Therefore, the expression phones -> 0 returns the first element in the phones array as a JSONB value.

To extract the first phone number as a text string, you can use the ->> operator:

SELECT
  name,
  phones ->> 0 phone
FROM
  employees
WHERE
  name = 'John Doe';

Output:

name   |     phone
----------+----------------
 John Doe | (408) 555-1111
(1 row)

3) Extracting the last array element example

The following example uses the -> operator to retrieve the first phone number of an employee with the name Jane Smith:

SELECT
  name,
  phones -> -1 phone
FROM
  employees
WHERE
  name = 'Jane Smith';

Output:

name    |      phone
------------+------------------
 Jane Smith | "(408) 666-3333"
(1 row)

To extract the last phone number as a JSONB value, you can use the ->> operator:

SELECT
  name,
  phones ->> -1 phone
FROM
  employees
WHERE
  name = 'Jane Smith';

Output:

name    |     phone
------------+----------------
 Jane Smith | (408) 666-3333
(1 row)

4) Extracting an element that does not exist

The following example uses the -> operator to retrieve the 4th phone number of an employee with the name Jane Smith:

SELECT
  name,
  phones -> 3 phone
FROM
  employees
WHERE
  name = 'Jane Smith';

Output:

name    | phone
------------+-------
 Jane Smith | null
(1 row)

Since Jane Smith has 3 phone numbers only, the query returns NULL.

Extracting object value

To extract a value of a JSON object by a key, you use the -> operator:

object -> 'key'

The -> operator returns the value of the ‘key’ as a JSONB value. If the key does not exist, the -> operator returns null.

If you want to return the value as an SQL value, you can use the ->> operator:

object ->> 'key'

Extracting JSON object value example

1) Setting up a sample table

First, create a new table called requests:

CREATE TABLE requests(
   id SERIAL PRIMARY KEY,
   employee_id INT NOT NULL,
   request_date DATE NOT NULL,
   data JSONB NOT NULL
);

Second, insert some rows into the requests table:

INSERT INTO requests (request_date, employee_id, data)
VALUES
   ('2024-02-23',1, '{"current_position": "Software Engineer", "new_position": "Senior Software Engineer", "effective_date": "2024-03-01"}'),
   ('2024-02-24',2, '{"current_position": "Data Analyst", "new_position": "Senior Data Analyst", "effective_date": "2024-03-15"}'),
   ('2024-02-25',3, '{"current_position": "Marketing Manager", "new_position": "Senior Marketing Manager", "effective_date": "2024-04-01"}')
RETURNING *;

Output:

id | employee_id | request_date |                                                         data
----+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------
  1 |           1 | 2024-02-23   | {"new_position": "Senior Software Engineer", "effective_date": "2024-03-01", "current_position": "Software Engineer"}
  2 |           2 | 2024-02-24   | {"new_position": "Senior Data Analyst", "effective_date": "2024-03-15", "current_position": "Data Analyst"}
  3 |           3 | 2024-02-25   | {"new_position": "Senior Marketing Manager", "effective_date": "2024-04-01", "current_position": "Marketing Manager"}
(3 rows)

2) Extract a value from a JSON object

The following example uses the -> operator to extract the current position of the request of employee ID 1:

SELECT
  data -> 'current_position' current_position
FROM
  requests
WHERE
  employee_id = 1;

Output:

current_position
---------------------
 "Software Engineer"
(1 row)

The return value is a JSONB value.

To get the current position as a text string, you can use the ->> operator:

SELECT
  data ->> 'current_position' current_position
FROM
  requests
WHERE
  employee_id = 1;

Output:

current_position
-------------------
 Software Engineer
(1 row)

2) Extract a key that does not exist

The following example attempts to extract a value of a non-existing key from a JSON object:

SELECT
  data ->> 'position' position
FROM
  requests
WHERE
  employee_id = 1;

Output:

position
----------
 null
(1 row)

Summary

Use the json_array -> n and json_array ->> n operator to extract a JSON array element as a JSONB value or as a text string specified by an index.
Use the json_object -> 'key' and json_object ->> 'key' operator to extract a value from an object specified by a key as a JSONB value and a text string.

PostgreSQL JSON Extract

Extracting elements from JSON arrays

Extracting JSON array element examples

1) Setting up a sample table

2) Extracting the first array element example

3) Extracting the last array element example

4) Extracting an element that does not exist

Extracting object value

Extracting JSON object value example

1) Setting up a sample table

2) Extract a value from a JSON object

2) Extract a key that does not exist

Summary

On this page