Json Function

The json(field, path) Function

The json(field, path) function extracts the value from the specified path in a JSON field and returns it as a separate field in the query response. It is used in the fields query parameter alongside regular field names and other field functions.

::callout{icon="material-symbols:warning-rounded" color="warning"}

Supported Paramaters the json(field, path) function is currently only supported for use in the fields query parameter.

::

Syntax

json(field, path)

• field — the name of a JSON column in the collection (or a relational path to one, see Relational Queries).
• path — a dot-and-bracket notation path to the value you want to extract from within the JSON document.

Both arguments are required and separated by a comma.

Path Notation

Paths use dot notation for object keys and bracket notation for array indices.

Examples:

json(metadata, color)                   → top-level key
json(metadata, settings.theme)          → nested object
json(data, items[0].name)               → array element property
json(data, [0])                         → first element of a top-level array

Response Format

Extracted values are returned as additional fields on each item using auto-generated aliases. The alias follows the pattern:

{field}_{path}_json

Special characters in the path ([, ], .) are replaced with underscores. For example:

Example Request and Response

GET /items/articles?fields=id,title,json(metadata, color)&sort=title

{
  "data": [
    {
      "id": 1,
      "title": "An Article",
      "metadata_color_json": "blue"
    }
  ]
}

Relational Queries

json(field, path) can traverse relational fields to extract JSON values from related items. The relational path goes inside the first argument, before the JSON field name.

Many-to-One (M2O)

json(relation.json_field, path)

The extracted value is returned nested under the relational key in the response, alongside any other requested fields from that relation.

GET /items/articles?fields=id,title,category_id.name,json(category_id.metadata, color)

{
  "data": [
    {
      "id": 1,
      "title": "An Article",
      "category_id": {
        "name": "Tech",
        "metadata_color_json": "blue"
      }
    }
  ]
}

Multiple json(field, path) extractions from the same relation are grouped under the same relational key:

GET /items/articles?fields=id,json(category_id.metadata, color),json(category_id.metadata, icon)

{
  "data": [
    {
      "category_id": {
        "metadata_color_json": "blue",
        "metadata_icon_json": "laptop"
      }
    }
  ]
}

One-to-Many (O2M)

For O2M relations, each related item returns its own extracted value. The response is an array of objects, each containing the extracted key.

GET /items/articles/1?fields=id,json(comments.data, type)

{
  "data": {
    "id": 1,
    "comments": [
      { "data_type_json": "review" },
      { "data_type_json": "feedback" },
      { "data_type_json": "question" }
    ]
  }
}

Many-to-Any (M2A)

For M2A relations, use the standard Directus collection scope syntax inside the first argument:

json(relation.item:collection_name.json_field, path)

GET /items/shapes/1?fields=id,json(children.item:circles.metadata, color)

Relational Depth Limit

json(field, path) will enforce a maximum relational depth (MAX_RELATIONAL_DEPTH, default 10) limit for the field argument. This depth is calculated irrespective of the Path depth limit mentioned below

json(category_id.metadata, a.b.c.d.e)

This has a relational depth of 2 (category_id + metadata), regardless of how many segments are in the JSON path a.b.c.d.e.

Exceeding the relational depth will return an error.

JSON Path Depth Limit

In addition to a relation depth, json(field, path) will also enforce a path depth limit (MAX_JSON_QUERY_DEPTH, default 10). This depth is calculated irrespective of the relational depth.

json(category_id.metadata, a[0].c.d.e.f.g.h.i.j)

The above example has a path depth of 10 and is allowed by default; adding one more segment exceeds the limit.

Exceeding the path depth limit returns an error.

Unsupported Path Expressions

The following path syntaxes are not supported and will return an error:

Object Keys with Special Characters

The json(field, path) path syntax uses . as a separator between key segments. There is no escape mechanism for object keys that themselves contain dots, spaces, or other special characters. For example, if your JSON has a key "first.name", there is no way to express that in the path — json(data, first.name) would be interpreted as nested access to key first, then key name.

Similarly, because MySQL and MariaDB path conversion uses dot-notation ($.key.subkey), keys containing characters that are special in that context (e.g., spaces) may not be reachable. PostgreSQL's parameterized ->? approach is more permissive for unusual key names, but the input path format still does not provide an escaping mechanism.

Database-Specific Exceptions

SQLite

• SQLite can return 0/1 isntead of boolean values.

MSSQL

• Will always returns scalar values as strings (NVARCHAR), even when the original JSON value is a number or boolean. For example, a JSON integer 42 will be returned as the string "42". Your application should perform type coercion as needed.

Oracle

• Similar to MSSQL will also return scalar values as strings, regardless of the original JSON type (number, boolean, etc.). A JSON number 3.14 will be returned as "3.14".