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 -> nCode language: SQL (Structured Query Language) (sql)

In this syntax, n locates the n^th 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 n^th element does not exist, the operator -> returns null. To extract an array element as a text string, you can use the ->> operator:

json_array ->> nCode language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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 *;Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

Output:

   name   |      phone
----------+------------------
 John Doe | "(408) 555-1111"
(1 row)Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

Output:

   name   |     phone
----------+----------------
 John Doe | (408) 555-1111
(1 row)Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

Output:

    name    |      phone
------------+------------------
 Jane Smith | "(408) 666-3333"
(1 row)Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

Output:

    name    |     phone
------------+----------------
 Jane Smith | (408) 666-3333
(1 row)Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

Output:

    name    | phone
------------+-------
 Jane Smith | null
(1 row)Code language: SQL (Structured Query Language) (sql)

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'Code language: JavaScript (javascript)

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'Code language: JavaScript (javascript)

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
);Code language: PHP (php)

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 *;Code language: SQL (Structured Query Language) (sql)

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)Code language: JavaScript (javascript)

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;Code language: SQL (Structured Query Language) (sql)

Output:

  current_position
---------------------
 "Software Engineer"
(1 row)Code language: JavaScript (javascript)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

 position
----------
 null
(1 row)Code language: JavaScript (javascript)

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.

Was this tutorial helpful ?