ftisiot ideas about food and life
who talks suggestions soft json-pg

How to parse JSON arrays in PostgreSQL?

Published Jan 9, 2023 by in Postgresql, Json, Jsonb, Parse, Array at https://ftisiot.net/postgresqljson/how-to-parse-json-arrays-in-postgresql/

PostgreSQLยฎ offers two types of data types to handle JSON data, JSON and JSONB, and provides several different ways to parse arrays from a JSON document:

  • the json_array_elements (jsonb_array_elements for JSONB columns) function to extract the array elements as JSON objects
  • the json_array_elements_text (jsonb_array_elements_text for JSONB columns) function to extract the array elements as text
  • the json_array_length (jsonb_array_length for JSONB columns) function to retrieve the length of the array

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

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 JSON array elements as JSON objects with the json_array_elements function

The pizzas items can be extracted creating one row per element using the json_array_elements (jsonb_array_elements since the column is defined as JSONB) function:

select p.*
from test 
cross join lateral jsonb_array_elements(
    json_data -> 'pizzas') p;

Result

                                value
----------------------------------------------------------------------
 {"pizzaName": "Salami", "additionalToppings": ["๐Ÿฅ“", "๐ŸŒถ๏ธ"]}
 {"pizzaName": "Margherita", "additionalToppings": ["๐ŸŒ", "๐ŸŒถ๏ธ", "๐Ÿ"]}
(2 rows)

Extract the JSON array elements as Text objects with the json_array_elements_text function

The phoneNumbers items can be extracted creating one row per element using the json_array_elements_text (jsonb_array_elements_text since the column is defined as JSONB) function:

select p.*
from test 
cross join lateral jsonb_array_elements_text(
    json_data -> 'phoneNumbers') p;

Result

       value
--------------------
 (935)503-3765x4154
 (935)12345
(2 rows)

Retrieve the legnth of the JSON array with the json_array_length function

The phoneNumbers array length can be retrieved using the json_array_length (jsonb_array_length since the column is defined as JSONB) function:

select 
    jsonb_array_length(
json_data -> 'phoneNumbers')
from test;

Result

 jsonb_array_length
--------------------
                  2
(1 row)

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


Francesco Tisiot

Mastodon Francesco comes from Verona, Italy and works as a Senior Developer Advocate at Aiven. With his many years of experience as a data analyst, he has stories to tell and advice for data-wranglers everywhere. Francesco loves sharing knowledge with others as a speaker and writer, and is on a mission to defend the world from bad Italian food!

Story logo

© 2023