How to extract a field from a JSON object in PostgreSQL®?

- 4 minutes read - 776 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides several different ways to extract fields from a JSON document:

  • the -> operator to extract the field as JSON
  • the -> operator to extract an item from an array
  • the ->> operator to extract the field as text
  • the #> operator to extract the object at a specific path as JSON
  • the #>> operator to extract the object at a specific path as text
  • the [] operator to extract the object at a specific path as JSON
  • the json_extract_path (jsonb_extract_path for JSONB columns) function to extract the object at a specific path as JSON

A quick overview of the methods is available in the video, scroll further for the written examples.

NOTE: To review the differences between JSON and JSONB check out the related article.

NOTE: more info is available in the PostgreSQL JSON functions documentation page

 

👉 Want a FREE PostgreSQL database?👈
🦀 Check Aiven’s FREE plans! 🦀

⚡️ Want to optimize your SQL query with AI? ⚡️
Check Aiven SQL Query Optimizer!

⚡️ Want a Fully PostgreSQL optimized Database? ⚡️
Check Aiven AI Database Optimizer!
Powered by EverSQL, it provides you index and SQL rewrite recommendations to take your database performance to the next level

The dataset

The dataset is the following:

{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056\nDPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}
Check out the description of the fields The following examples use a pizza order dataset with an order having:
  • id: 778
  • shop: “Luigis Pizza”
  • name: “Edward Olson”
  • phoneNumbers:["(935)503-3765x4154","(935)12345"]
  • address: “Unit 9398 Box 2056\nDPO AP 24022”
  • image: null
  • and two pizzas contained in the pizzas item:
[
    {
        "pizzaName": "Salami",
        "additionalToppings": ["🥓", "🌶️"]
    },
    {
        "pizzaName": "Margherita",
        "additionalToppings": ["🍌", "🌶️", "🍍"]
    }
]
If you want to reproduce the examples, check how to recreate the dataset

It can be recreated with the following script:

create table test(id serial, json_data jsonb);

insert into test(json_data) values (
'{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056\nDPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}');

Extract a JSON field using the -> operator

To extract a field from a JSON document, you can use the -> operator. The id and name fields can be extracted with:

select 
    json_data -> 'id' id,
    json_data -> 'name' name
    from test;

Result

 id  |      name
-----+----------------
 778 | "Edward Olson"

Extract an item from a JSON array with the -> operator

The -> operator works with:

  • text to identify a field name
  • integers to identify an item within an array

Therefore the second pizza in the order can be extracted with

select 
    json_data -> 'pizzas' -> 1 as second_pizza
    from test;

In the above query

  • json_data -> 'pizzas' extracts the pizzas field
  • the additional -> 1 extracts the second pizza (index starts from 0)

Result

                             second_pizza
----------------------------------------------------------------------
 {"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}
(1 row)

Extract a JSON field as Text using the ->> operator

To extract a field from a JSON document as Text, you can use the -> operator. The id and name fields can be extracted with:

select 
    json_data ->> 'id' id,
    json_data ->> 'name' order_name
from test;

Result:

 id  |  order_name
-----+--------------
 778 | Edward Olson

Extract a JSON field as a specific path using the @> operator

The -> operator allows only to extract “first level” fields, to extract in only one pass more nested items you can use the @> operator.

E.g. to extract the additionalToppings belonging to the second pizza in the order:

select 
    json_data #> 
       '{pizzas,1,additionalToppings}'  
        as additional_toppings_2nd_pizza
from test;

The {pizzas,1,additionalToppings} is the concatenation of:

  • pizzas: the field name
  • 1: the second item in the pizzas array (index starts from 0)
  • additionalToppings: the field name within the pizzas

Results

 additional_toppings_2nd_pizza
-------------------------------
 ["🍌", "🌶️", "🍍"]
(1 row)

Extract a JSON field as a specific path using the [] operator (subscripting)

To get the second pizza name in the order you can use the [] operator

select 
    json_data['pizzas']['1']['pizzaName'] second_pizza_name
from test;

Result

 second_pizza_name
-------------------
 "Margherita"
(1 row)

Extract a JSON field as a specific path using the json_extract_path function

To get the second pizza name in the order you can use the json_extract_path (jsonb_extract_path since the column is defined as JSONB)

select 
    jsonb_extract_path(json_data, 'pizzas', '1', 'pizzaName') second_pizza_name
from test;

Result

 second_pizza_name
-------------------
 "Margherita"
(1 row)

Review all the JSON PostgreSQL use-cases listed in the main page