In Neon, you can recover a 100 TB database in 1 second. No more fear of breaking production
16kLog InSign Up
PostgreSQL Tutorial/JSON Functions/Extracting JSON Data

PostgreSQL JSON Extract

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.
PreviousPostgreSQL jsonb_extract_path_text() Function
NextPostgreSQL jsonb_insert() Function

Last updated on

Was this page helpful?

On this page

Edit this page on GitHub