How to parse JSON keys in PostgreSQL®

- 3 minutes read - 597 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use several functions to extract the keys and related values in tabular format.

  • json_each (jsonb_each for the JSONB type) to extract the key as text and the value as separate JSON object
  • json_each_text (jsonb_each_text for the JSONB type) to extract the key as text and the value as separate text object
  • json_object_keys (jsonb_object_keys for the JSONB type) to extract the keys

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 the key as text and the value as JSON object with the json_each function

The keys and related values can be extracted with the json_each (jsonb_each since the column is defined as JSONB) function

select p.*
from test 
cross join lateral jsonb_each(json_data) p;

Result

     key      |                                                               value
--------------+------------------------------------------------------------------------------------------------------------------------------------
 id           | 778
 name         | "Edward Olson"
 shop         | "Luigis Pizza"
 image        | null
 pizzas       | [{"pizzaName": "Salami", "additionalToppings": ["🥓", "🌶️"]}, {"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}]
 address      | "Unit 9398 Box 2056\nDPO AP 24022"
 phoneNumbers | ["(935)503-3765x4154", "(935)12345"]
(7 rows)

Extract the key as text and the value as text object with the json_each_text function

The keys and related values can be extracted with the json_each_text (jsonb_each_text since the column is defined as JSONB) function

select p.*
from test 
cross join lateral jsonb_each_text(json_data) p;

Result

     key      |                                                               value
--------------+------------------------------------------------------------------------------------------------------------------------------------
 id           | 778
 name         | Edward Olson
 shop         | Luigis Pizza
 image        | 👻
 pizzas       | [{"pizzaName": "Salami", "additionalToppings": ["🥓", "🌶️"]}, {"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}]
 address      | Unit 9398 Box 2056                                                                                                                +
              | DPO AP 24022
 phoneNumbers | ["(935)503-3765x4154", "(935)12345"]
(7 rows)

NOTE: compared to json_each, the json_each_text sets the output of the value column as text rather than JSON (or JSONB)

Extract the JSON keys with the json_object_keys function

The keys can be extracted with the json_object_keys (jsonb_object_keys since the column is defined as JSONB) function

select 
    jsonb_object_keys(json_data) 
from test;

Result

 jsonb_object_keys
-------------------
 id
 name
 shop
 image
 pizzas
 address
 phoneNumbers
(7 rows)

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