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:

[
    {
        "model": "core.location",
        "pk": 119,
        "fields": {
            "name": "Vista Community Clinic- The Gary Center, S. Harbour Blvd",
            "full_address": "201 S. Harbor Boulevard, \nLa Habra, CA 90631"
        }
    }
]

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

select
  object_id,
  content_type_id,
  json_extract_path(
    serialized_data::json,
    '0',
    'fields',
    'full_address'
  ) as full_address
from
  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.

Related

• sqlite json_extract() path syntax in SQLite - 2022-01-18
• postgresql Constructing GeoJSON in PostgreSQL - 2021-04-24
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• jq Flattening nested JSON objects with jq - 2021-03-11
• sqlite Null case comparisons in SQLite - 2020-04-21
• sqlite Splitting on commas in SQLite - 2021-02-01
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• json JSON Pointer - 2022-11-14
• gis Using SQL with GDAL - 2023-03-09
• postgresql Using unnest() to use a comma-separated string as the input to an IN query - 2021-04-10

Created 2021-04-13T17:29:22-07:00 · Edit