How to tabulate a JSON to a record in PostgreSQL®

- 3 minutes read - 491 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides two different ways to tabulate a JSON to a record:

  • the json_populate_record (jsonb_populate_record for JSONB) function using an existing database type
  • the json_to_record (jsonb_to_record for JSONB) function defining the list of fields

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": ["🍌", "🌶️", "🍍"]
        }
    ]
}');

Tabulate a JSON to a record using an existing database type with the json_populate_record function

To tabulate a JSON document to a table record using an existing database type you can use the json_populate_record (jsonb_populate_record in the example for JSONB column). To tabulate the JSON document:

  • Create a dedicated database type
create type pizzaOrder as (id int, name text, address text);
  • Tabulate the JSON using the dedicated function
select p.* 
from test cross join lateral 
    jsonb_populate_record(
        null::pizzaOrder, json_data) p;

Result

 id  |     name     |      address
-----+--------------+--------------------
 778 | Edward Olson | Unit 9398 Box 2056+
     |              | DPO AP 24022

Tabulate a JSON to a record defining the list of fields with the json_to_record function

To tabulate a JSON document to a table record defining the list of fields you can use the json_to_record (jsonb_to_record in the example for JSONB column). To tabulate the JSON document:

select p.* from test
cross join lateral 
    jsonb_to_record(json_data) 
    as p(id int, "phoneNumbers" text[]);

Result

 id  |          phoneNumbers
-----+---------------------------------
 778 | {(935)503-3765x4154,(935)12345}

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