Introducing the JSONata Extension for DuckDB by Query.Farm!

Transform and query JSON data with elegant expressions—all within SQL. No more fighting with nested JSON or writing complex extraction logic.

What does it do?

✅ Extract values from deeply nested JSON structures ✅ Transform and reshape JSON documents on the fly ✅ Filter arrays with sophisticated conditions ✅ Create new JSON structures from existing data ✅ Perform calculations and aggregations on JSON

Real-world use cases:

🔹 Parse API responses and extract exactly what you need 🔹 Transform JSON logs for analysis 🔹 Reshape JSON documents to match target schemas 🔹 Query complex configuration files 🔹 Extract property names from JSON schemas 🔹 Filter and aggregate JSON arrays with ease

Quick example:

INSTALL jsonata FROM community; LOAD jsonata;

— Extract mobile number from nested array SELECT jsonata(  ‘Phone[type = “mobile”].number’,  ’{“Phone”: [    {“type”: “home”, “number”: “0203 544 1234”},    {“type”: “mobile”, “number”: “077 7700 1234”}  ]}’ ); — Returns: “077 7700 1234”

— Transform JSON into a new structure SELECT jsonata(’{  “name”: FirstName & ” ” & Surname,  “mobile”: Phone[type = “mobile”].number }’, your_json_data);

JSONata is a lightweight query language inspired by XPath, designed specifically for JSON. It’s incredibly powerful yet intuitive—think “SQL for JSON” but with even more elegance.

Check it out: https://query.farm/duckb_extension_jsonata.html

Try JSONata in the interactive playground: https://try.jsonata.org

Available now in the DuckDB Community Extensions repository!

Originally posted on LinkedIn.