Add JSON_EXTRACT ES|QL scalar function by quackaplop · Pull Request #141507 · elastic/elasticsearch

quackaplop · 2026-01-29T11:27:18Z

Superseded by #142375

cla-checker-service · 2026-01-29T11:27:23Z

❌ Author of the following commits did not sign a Contributor Agreement:
3cd47c6

Please, read and sign the above mentioned agreement if you want to contribute to this project

github-actions · 2026-01-29T11:29:44Z

🔍 Preview links for changed docs

docs/reference/query-languages/esql/kibana/docs/functions/json_extract.md

github-actions · 2026-01-29T11:29:45Z

ℹ️ Important: Docs version tagging

👋 Thanks for updating the docs! Just a friendly reminder that our docs are now cumulative. This means all 9.x versions are documented on the same page and published off of the main branch, instead of creating separate pages for each minor version.

We use applies_to tags to mark version-specific features and changes.

Expand for a quick overview

When to use applies_to tags:

✅ At the page level to indicate which products/deployments the content applies to (mandatory)
✅ When features change state (e.g. preview, ga) in a specific version
✅ When availability differs across deployments and environments

What NOT to do:

❌ Don't remove or replace information that applies to an older version
❌ Don't add new information that applies to a specific version without an applies_to tag
❌ Don't forget that applies_to tags can be used at the page, section, and inline level

🤔 Need help?

Check out the cumulative docs guidelines
Reach out in the #docs Slack channel

Implements a new ES|QL function that extracts values from JSON strings using dot-notation path expressions. Supports keyword, text, and _source input types.

alex-spies

Small drive by as I was looking into something related.

Is this ready for review? If we add the :Analytics/ES|QL label, the team will be pinged :)

alex-spies · 2026-02-02T11:00:45Z

docs/reference/query-languages/esql/kibana/definition/functions/json_extract.json

+    "ROW json = \"{\\\\\"name\\\\\":\\\\\"Alice\\\\\",\\\\\"age\\\\\":30}\"\n| EVAL name = JSON_EXTRACT(json, \"name\")",
+    "ROW json = \"{\\\\\"user\\\\\":{\\\\\"address\\\\\":{\\\\\"city\\\\\":\\\\\"London\\\\\"}}}\"\n| EVAL city = JSON_EXTRACT(json, \"user.address.city\")"
+  ],
+  "preview" : false,


Straight to GA intended?

Probably not: FN_JSON_EXTRACT(Build.current().isSnapshot())

Nope! Thank you

Fixed in the new PR #142375. Added preview = true to the @FunctionInfo annotation and moved registration to snapshotFunctions(). The generated JSON now correctly shows "preview": true, "snapshot_only": true.

alex-spies · 2026-02-02T11:03:28Z

x-pack/plugin/esql/qa/testFixtures/src/main/resources/json_extract.csv-spec

I think we should add cases for when a property is duplicated.

This is the current behavior:

row x = "{\"foo\":1, \"foo\":2}" | eval y = json_extract(x, "foo")::integer x | y ------------------+--------------- {"foo":1, "foo":2}|1

Should this give a warning?

Also, this is different from both jq and psql. Both use the last definition of a property, not the first.

Added test cases for duplicate keys in #142375. Current behavior is first-match (streaming parser stops at the first matching key). This is documented in both the csv-spec and unit tests. We can revisit switching to last-match semantics (to align with jq/psql) as a follow-up if desired — it would require continuing past the first match in the streaming parser rather than returning immediately.

alex-spies · 2026-02-02T11:05:58Z

x-pack/plugin/esql/qa/testFixtures/src/main/resources/json_extract.csv-spec

+
+jsonExtractJsonNull
+required_capability: fn_json_extract
+ROW json = "{\"value\":null}"


Another interesting case is a null inside a JSON array.

Added in #142375. Test case extracts index 1 from [1, null, 3] — returns ES|QL null without a warning, consistent with how we handle JSON null values elsewhere. Also updated the @FunctionInfo description and generated docs to document this behavior.

quackaplop · 2026-02-02T11:08:17Z

Not yet. But I will publish it this week.

elasticsearchmachine · 2026-02-02T15:17:54Z

Pinging @elastic/es-analytical-engine (Team:Analytics)

idegtiarenko · 2026-02-02T15:21:17Z

x-pack/plugin/esql/src/main/java/org/elasticsearch/xpack/esql/action/EsqlCapabilities.java

-         * Support for requesting the "_size" metadata field when the mapper-size plugin is enabled.
-         */
-        METADATA_SIZE_FIELD,
+        FN_JSON_EXTRACT(Build.current().isSnapshot()),


Was PERIODIC_EMIT_PARTIAL_AGGREGATION_RESULTS intentionally removed?

No, that was accidental — our commit replaced it instead of appending. Fixed in #142375 by rebasing onto latest main. FN_JSON_EXTRACT is now appended at the end of the capabilities list, all existing capabilities are preserved.

idegtiarenko · 2026-02-02T15:28:45Z

...rc/main/java/org/elasticsearch/xpack/esql/expression/function/scalar/string/JsonExtract.java

+        }
+        // Convert bracket notation to dot notation: "orders[1].item" -> "orders.1.item"
+        String converted = path.replace("[", ".").replace("]", "");
+        return converted.split("\\.");


String.split is fairly expensive considering its argument is a regexp pattern that needs to be parsed.
Additionally I expect most of the cases the actual path used with this function is going to be a foldable constant (opposed to be something derived per document).
With this I suggest we have a specialized version that only process path once.

You could find examples for constant specialization in org.elasticsearch.xpack.esql.expression.function.scalar.string.Hash (process and processConstant)

Oh, this code is a nightmare. I am not done with it yet - there are SO MANY memory copies there

Fixed in #142375. Added a processConstant evaluator with a @Fixed ParsedPath parameter (following the Hash.process/Hash.processConstant pattern). When the path is a foldable constant, it's parsed once into a ParsedPath record and reused across all rows. Also replaced String.split(regex) with manual character iteration to avoid regex compilation overhead in the non-constant case as well.

getkub · 2026-02-08T08:34:52Z

This is a great functionality. But as mentioned in the issue, can it be developed in similar to 'jq' that way developers don't need to learn another format of usage?

quackaplop · 2026-02-12T10:23:11Z

Superseded by #142375 (moved to a clean branch with correct authorship).

quackaplop · 2026-02-19T12:26:28Z

Correct, fixed in #142375. Now properly marked as preview = true and registered in snapshotFunctions().

elasticsearchmachine added needs:triage Requires assignment of a team area label v9.4.0 labels Jan 29, 2026

Add JSON_EXTRACT ES|QL scalar function

3cd47c6

Implements a new ES|QL function that extracts values from JSON strings using dot-notation path expressions. Supports keyword, text, and _source input types.

quackaplop force-pushed the pr/json-extract branch from 272314c to 3cd47c6 Compare January 29, 2026 13:27

alex-spies reviewed Feb 2, 2026

View reviewed changes

idegtiarenko added >feature Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo) :Analytics/ES|QL AKA ESQL labels Feb 2, 2026

elasticsearchmachine removed the needs:triage Requires assignment of a team area label label Feb 2, 2026

idegtiarenko reviewed Feb 2, 2026

View reviewed changes

quackaplop marked this pull request as draft February 2, 2026 15:50

quackaplop closed this Feb 12, 2026

quackaplop mentioned this pull request Feb 12, 2026

Add JSON_EXTRACT ES|QL scalar function #142375

Merged

Conversation

quackaplop commented Jan 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Superseded by #142375

Uh oh!

cla-checker-service bot commented Jan 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

github-actions bot commented Jan 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

🔍 Preview links for changed docs

Uh oh!

github-actions bot commented Jan 29, 2026

ℹ️ Important: Docs version tagging

When to use applies_to tags:

What NOT to do:

🤔 Need help?

Uh oh!

alex-spies left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

quackaplop commented Feb 2, 2026

Uh oh!

elasticsearchmachine commented Feb 2, 2026

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

getkub commented Feb 8, 2026

Uh oh!

quackaplop commented Feb 12, 2026

Uh oh!

quackaplop commented Feb 19, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

quackaplop commented Jan 29, 2026 •

edited

Loading

cla-checker-service bot commented Jan 29, 2026 •

edited

Loading

github-actions bot commented Jan 29, 2026 •

edited

Loading