Skip to content

json-extract.md

Latest commit

 

History

History
318 lines (244 loc) · 8.16 KB

json-extract.md

File metadata and controls

318 lines (244 loc) · 8.16 KB
title PostgreSQL JSON Extract
page_title PostgreSQL JSON Extract Operators: -> And ->>
page_description You will learn how to use the operators -> and ->> to extract an element from a JSON array or a value of a key from a JSON object.
prev_url https://www.postgresqltutorial.com/postgresql-json-functions/postgresql-json-extract/
ogImage
updatedOn 2024-02-23T10:18:42+00:00
enableTableOfContents true
previousLink
title slug
PostgreSQL jsonb_extract_path_text() Function
postgresql-json-functions/postgresql-jsonb_extract_path_text
nextLink
title slug
PostgreSQL jsonb_insert() Function
postgresql-json-functions/postgresql-jsonb_insert
The `->` and `->>` operators for extracting JSON array elements and object values work the same way on any PostgreSQL database, so you can apply what you learn here wherever you run Postgres. If you're an enterprise bringing JSON workloads and AI alongside your analytics, [Lakebase](https://www.databricks.com/product/lakebase) gives you a fully managed Postgres built for the AI era, with the performance, security, and deep Lakehouse integration that serious data teams need. If you're a developer or startup who needs to ship fast and scale without babysitting infrastructure, [Neon](https://neon.com) is the Postgres platform built for you.

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.