Using json_extract_path in PostgreSQL#

The json_extract_path() function in PostgreSQL can be used to extract specific items from JSON - but I couldn’t find documentation for the path language it uses.

It turns out it’s a variadic functions - it takes multiple arguments, so the path you want is split into separate arguments.

I had data that looks like this (from django-reversion) in a column called serialized_data:

1
[
2
    {
3
        "model": "core.location",
4
        "pk": 119,
5
        "fields": {
6
            "name": "Vista Community Clinic- The Gary Center, S. Harbour Blvd",
7
            "full_address": "201 S. Harbor Boulevard, \nLa Habra, CA 90631"
8
        }
9
    }
10
]

I wanted just that full_address value. Here’s how I got it:

1
select
2
  object_id,
3
  content_type_id,
4
  json_extract_path(
5
    serialized_data::json,
6
    '0',
7
    'fields',
8
    'full_address'
9
  ) as full_address
10
from
11
  reversion_version

That’s a path of 0, fields, full_address - note that arrays are accessed by passing a string integer.

The ::json casting operater is required here because my JSON isn’t stored in a PostgreSQL jsonb column, it’s stored in a regular text column.

Without the ::json I got the following error:

function json_extract_path(text, unknown, unknown, unknown) does not exist LINE 7: json_extract_path( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.