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 aJSONB value, you use the-> operator.
Here’s the syntax for using the-> operator:
json_array -> nIn 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-> returnsnull. To extract an array element as a text string, you can use the->> operator:
json_array->> nExtracting 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 calledemployees to store employee data:
CREATE TABLE employees( idSERIAL PRIMARY KEY, name VARCHAR(255)NOT NULL, phones JSONBNOT NULL);Second,insert some rows into theemployees 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 phoneFROM employeesWHERE 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 expressionphones -> 0 returns the first element in thephones array as aJSONB value.
To extract the first phone number as a text string, you can use the ->> operator:
SELECT name, phones->> 0 phoneFROM employeesWHERE 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 nameJane Smith:
SELECT name, phones-> -1 phoneFROM employeesWHERE name = 'Jane Smith';Output:
name | phone------------+------------------ Jane Smith | "(408) 666-3333"(1 row)To extract the last phone number as aJSONB value, you can use the ->> operator:
SELECT name, phones->> -1 phoneFROM employeesWHERE 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 nameJane Smith:
SELECT name, phones-> 3 phoneFROM employeesWHERE name = 'Jane Smith';Output:
name | phone------------+------- Jane Smith | null(1 row)Since Jane Smith has 3 phone numbers only, the query returnsNULL.
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 calledrequests:
CREATE TABLE requests( idSERIAL PRIMARY KEY, employee_idINT NOT NULL, request_dateDATE NOT NULL, data JSONBNOT NULL);Second, insert some rows into therequests 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_positionFROM requestsWHERE 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_positionFROM requestsWHERE 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' positionFROM requestsWHERE employee_id = 1;Output:
position---------- null(1 row)Summary
- Use the
json_array -> nandjson_array ->> noperator to extract a JSON array element as aJSONBvalue or as a text string specified by an index. - Use the
json_object -> 'key'andjson_object ->> 'key'operator to extract a value from an object specified by a key as a JSONB value and a text string.
Last updated on