Shape / columns · All operations

Extract values by path

A variant of Parse nested values

Pull the values at chosen JSON paths, XPaths or field names out of a nested column into new columns.

Status

In arcform

Not built yet

arcform does not do this yet. Its place on the roadmap

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
Apache NiFi (3)
  • EvaluateJsonPathread 2026-09-27

    Evaluate JsonPath expressions against JSON content; results go to attributes or replace content

  • EvaluateXPathread 2026-09-27

    Evaluate XPath expressions against XML content; results go to attributes or replace content

  • EvaluateXQueryread 2026-09-27

    Evaluate XQuery expressions against XML content; results go to attributes or replace content

Apache SeaTunnel (1)
  • JsonPathread 2026-09-27

    Extract values from a JSON field by JsonPath into new typed fields

AWS Glue (1)
Duckle (1)
EasyMorph (1)
Ibis (2)
  • JSONGetItemread 2026-09-27

    Get a value from a JSON object by key, or from a JSON array by index

  • StructFieldread 2026-09-27

    Extract one named field from a struct value

KNIME Analytics Platform (3)
  • JSON Pathread 2026-09-27

    Extract values from a JSON column by JSONPath queries into new columns

  • JSON Path (Dictionary)read 2026-09-27

    Extract JSON values using JSONPath queries supplied in a second table

  • XPathread 2026-09-27

    Run XPath queries on an XML column into new columns

OpenRefine (1)
Polars (1)
  • Expr.struct (group)read 2026-09-27

    7 expressions: field, unnest, rename_fields, with_fields, json_encode and others; read and edit struct fields

RapidMiner Studio (3)
  • Extract Contentread 2026-09-27

    Extract the main text blocks from an HTML document, skipping short boilerplate

  • Extract Informationread 2026-09-27

    Extract values from a document into metadata by string match, regex, XPath or index

  • Generate Extractread 2026-09-27

    Add columns extracted from a text column by XPath, JSONPath, regex or string matching

Talend (2)
  • tExtractJSONFieldsread 2026-09-27

    Extract values from a JSON column by JSONPath or XPath queries

  • tExtractXMLFieldread 2026-09-27

    Extract values from an XML column into the output schema

VisiData (2)

Background

JSONPath defines a string syntax for selecting and extracting JavaScript Object Notation (JSON) values from within a given JSON value.[1] XML Path Language (XPath) navigates a tree representation of an Extensible Markup Language (XML) document, selecting nodes by a variety of criteria.[2]

When to use it

DuckDB's JSON data type uses 0-based indexing, while list[index] on a DuckDB LIST uses a 1-based index.[3][4] Check the index base whenever a path moves between a JSON value and a list. (Meridian's view)

In DuckDB, the -> operator has low precedence because it is also used in lambda functions, so an equality comparison on its result needs parentheses.[3] DuckDB's json_exists returns whether a path exists in a JSON value, and json_value returns NULL when the value at the path is not a scalar.[3] Test with json_exists where a field that is missing must be told apart from a field that holds null. (Meridian's view)

The method

DuckDB accepts two notations for a location in JSON: JSON Pointer, which separates fields with /, and JSONPath, which starts with $, separates fields with . and reaches array elements with [i].[3] JSON Pointer is a string syntax for identifying a specific value within a JSON document, published by the Internet Engineering Task Force in April 2013.[5] In a JSON Pointer, ~ is written as ~0 and / as ~1 when they appear inside a reference token.[5]

The JSONPath standard builds on Stefan Gössner's JSONPath proposal dated 2007-02-21, which was inspired by XML's XPath.[1] As JSONPath was implemented in more environments, its expressions became decreasingly portable, and the standard aims to serve as a common specification across languages and environments.[1] In standard JSONPath, a negative index selector counts back from the end of an array, so -1 selects the last element.[1] DuckDB's JSONPath reaches the back of a list with #, so $.duck[#-1] returns the last element.[3]

XPath was defined by the World Wide Web Consortium (W3C) in 1999.[2] XPath 3.1, a W3C Recommendation of 21 March 2017, supports JSON as well as XML.[6] In DuckDB, a value is retrieved from a STRUCT by dot notation, bracket notation or struct_extract.[7]

Sources

  1. [1]RFC 9535: JSONPath: Query Expressions for JSONstandard
  2. [2]XPath - Wikipediasecondary source
  3. [3]JSON Processing Functions – DuckDBdocumentation
  4. [4]List Functions – DuckDBdocumentation
  5. [5]RFC 6901: JavaScript Object Notation (JSON) Pointerstandard
  6. [6]XML Path Language (XPath) 3.1standard
  7. [7]Struct Data Type – DuckDBdocumentation