JSONata DuckDB Extension

The JSONata extension, developed by Query.Farm, brings the power of JSONata query and transformation language directly to your SQL queries in DuckDB. Transform, query, and manipulate JSON data with sophisticated expressions—all without leaving your database environment.

JSONata is a lightweight query and transformation language for JSON data, inspired by the location path semantics of XPath. Whether you’re extracting nested values, restructuring JSON documents, or performing complex transformations, JSONata makes JSON manipulation in SQL elegant and efficient.

What is JSONata?

JSONata is a sophisticated query and transformation language for JSON data. It allows you to:

Extract values from complex nested JSON structures
Transform and reshape JSON documents
Filter and aggregate JSON arrays
Perform calculations and string manipulations
Create new JSON structures from existing data

Learn more at https://jsonata.org

Use Cases

The JSONata extension is perfect for:

JSON data extraction: Pull specific values from deeply nested JSON structures
Data transformation: Reshape and restructure JSON documents to match target schemas
ETL pipelines: Transform JSON data during ingestion or export
API response processing: Extract and transform data from API responses stored as JSON
Schema analysis: Query JSON schema documents to extract property names and structure
Log analysis: Parse and transform JSON log entries
Configuration management: Extract and transform configuration data stored as JSON

Installation

jsonata is a DuckDB Community Extension.

You can install and load it with:

INSTALL jsonata FROM community;
LOAD jsonata;

Quick Start

-- Install and load the extension
INSTALL jsonata FROM community;
LOAD jsonata;

-- Extract a simple value
SELECT jsonata('Account', '{"Account": 5}');
┌──────────────────────────────────────┐
│ jsonata('Account', '{"Account": 5}') │
│                 json                 │
├──────────────────────────────────────┤
│ 5                                    │
└──────────────────────────────────────┘


-- Extract and transform data
SELECT jsonata('FirstName & " " & Surname',
  '{"FirstName": "Fred", "Surname": "Smith"}') as r;
┌──────────────┐
│      r       │
│     json     │
├──────────────┤
│ "Fred Smith" │
└──────────────┘

-- Create new JSON structure from existing data
SELECT jsonata('{
  "name": FirstName & " " & Surname,
  "mobile": Phone[type = "mobile"].number
}', '{
  "FirstName": "Fred",
  "Surname": "Smith",
  "Phone": [
    {"type": "home", "number": "0203 544 1234"},
    {"type": "mobile", "number": "077 7700 1234"}
  ]
}') as r
┌────────────────────────────────────────────────┐
│                       r                        │
│                      json                      │
├────────────────────────────────────────────────┤
│ {"mobile":"077 7700 1234","name":"Fred Smith"} │
└────────────────────────────────────────────────┘

Function

`jsonata(expression, json_data)`

The JSONata extension provides a single scalar function that evaluates a JSONata expression against JSON data.

Parameters: - expression (VARCHAR): The JSONata expression to evaluate - json_data (JSON): The JSON data to query/transform

Returns: JSON

Examples:

1. Extract a Simple Value

SELECT jsonata('Account', '{"Account": 5}') as r
┌──────┐
│  r   │
│ json │
├──────┤
│ 5    │
└──────┘

Extract a single value from a JSON object using the field name.

2. String Concatenation

SELECT jsonata(
  'FirstName & " " & Surname',
  '{"FirstName": "Fred", "Surname": "Smith"}'
);
┌──────────────┐
│      r       │
│     json     │
├──────────────┤
│ "Fred Smith" │
└──────────────┘

Combine multiple fields using the & concatenation operator.

3. Filter and Extract from Arrays

SELECT jsonata(
  'Phone[type = "mobile"].number',
  '{
    "Phone": [
      {"type": "home", "number": "0203 544 1234"},
      {"type": "office", "number": "01962 001234"},
      {"type": "mobile", "number": "077 7700 1234"}
    ]
  }'
);
┌─────────────────┐
│        r        │
│      json       │
├─────────────────┤
│ "077 7700 1234" │
└─────────────────┘

Filter an array by a condition and extract specific fields.

4. Create New JSON Structures

SELECT jsonata(
  '{
    "name": FirstName & " " & Surname,
    "mobile": Phone[type = "mobile"].number
  }',
  '{
    "FirstName": "Fred",
    "Surname": "Smith",
    "Age": 28,
    "Address": {
      "Street": "Hursley Park",
      "City": "Winchester",
      "Postcode": "SO21 2JN"
    },
    "Phone": [
      {"type": "home", "number": "0203 544 1234"},
      {"type": "office", "number": "01962 001234"},
      {"type": "mobile", "number": "077 7700 1234"}
    ]
  }'
) as r;
┌────────────────────────────────────────────────┐
│                       r                        │
│                      json                      │
├────────────────────────────────────────────────┤
│ {"mobile":"077 7700 1234","name":"Fred Smith"} │
└────────────────────────────────────────────────┘

Transform input JSON into a completely new structure, selecting and combining fields as needed.

5. Query Nested Structures with Wildcards

SELECT jsonata(
  '**.properties ~> $keys()',
  '{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "properties": {
      "Account": {
        "type": "object",
        "properties": {
          "Customer": {
            "type": "object",
            "properties": {
              "First Name": {"type": "string"},
              "Surname": {"type": "string"}
            }
          },
          "AccID": {"type": "string"},
          "Order": {
            "type": "array",
            "items": {
              "type": "object",
              "properties": {
                "OrderID": {"type": "string"},
                "Product": {"type": "array"}
              }
            }
          }
        }
      }
    }
  }'
) as r;
┌───────────────────────────────────────────────────────────────────────────────────┐
│                                         r                                         │
│                                       json                                        │
├───────────────────────────────────────────────────────────────────────────────────┤
│ ["AccID","Account","Customer","First Name","Order","OrderID","Product","Surname"] │
└───────────────────────────────────────────────────────────────────────────────────┘

Use the descendant wildcard operator (**) to traverse all levels of a nested structure and extract all property keys.

Practical Examples

Extract Data from API Responses

-- Extract specific fields from stored API responses
CREATE TABLE api_responses (
  id INTEGER,
  response JSON
);

INSERT INTO api_responses VALUES
  (1, '{"user": {"name": "Alice", "email": "alice@example.com", "roles": ["admin", "user"]}}');

SELECT
  id,
  jsonata('user.name', response) as name,
  jsonata('user.roles[0]', response) as primary_role
FROM api_responses;

Transform JSON Arrays

-- Transform an array of objects
SELECT jsonata(
  'products.{
    "item": name,
    "cost": price * 1.2,
    "available": inStock
  }',
  '{
    "products": [
      {"name": "Widget", "price": 10.00, "inStock": true},
      {"name": "Gadget", "price": 25.00, "inStock": false}
    ]
  }'
) as r;
┌──────────────────────────────────────────────────────────────────────────────────────────────┐
│                                              r                                               │
│                                             json                                             │
├──────────────────────────────────────────────────────────────────────────────────────────────┤
│ [{"available":true,"cost":12,"item":"Widget"},{"available":false,"cost":30,"item":"Gadget"}] │
└──────────────────────────────────────────────────────────────────────────────────────────────┘

Extract Deeply Nested Values

-- Navigate complex nested structures
SELECT jsonata(
  'Account.Order[0].Product[0]."Product Name"',
  '{
    "Account": {
      "Order": [
        {
          "OrderID": "order1",
          "Product": [
            {"Product Name": "Super Widget", "Price": 99.99}
          ]
        }
      ]
    }
  }'
) as r;
┌────────────────┐
│       r        │
│      json      │
├────────────────┤
│ "Super Widget" │
└────────────────┘

JSONata Expression Syntax

JSONata provides a rich set of operators and functions. Here are some commonly used features:

Operators

& - String concatenation: FirstName & " " & LastName
+, -, *, / - Arithmetic operations
=, !=, <, <=, >, >= - Comparison operators
and, or - Boolean logic

Functions

$count() - Count array elements
$sum() - Sum numeric values
$keys() - Get object keys
$uppercase(), $lowercase() - String transformations
$substring() - Extract substrings
Many more available at https://docs.jsonata.org/

Object Construction

{
  "newField": expression,
  "anotherField": expression
}

For complete syntax and function reference, visit the JSONata documentation.

Performance Considerations

Constant Expressions: When the JSONata expression is constant (same for all rows), the extension optimizes by parsing the expression once and reusing it for all rows
Complex Expressions: Very complex JSONata expressions or deeply nested JSON documents may require more processing time
Large JSON Documents: Performance scales with the size and complexity of both the JSONata expression and the JSON data

Working with Tables

You can use JSONata with JSON columns in your tables:

-- Create a table with JSON data
CREATE TABLE events (
  id INTEGER,
  event_data JSON
);

-- Extract and transform data using JSONata
SELECT
  id,
  jsonata('event.type', event_data) as event_type,
  jsonata('event.timestamp', event_data) as timestamp,
  jsonata('user.name', event_data) as user_name
FROM events;

-- Transform entire JSON documents
SELECT
  id,
  jsonata('{
    "type": event.type,
    "user": user.name,
    "location": event.location.city
  }', event_data) as summary
FROM events;

Learn More

JSONata Official Site: https://jsonata.org
JSONata Documentation: https://docs.jsonata.org
JSONata Playground: https://try.jsonata.org - Interactive tool to test JSONata expressions

Love ❤️ this DuckDB extension? You’ll Love This.

Get the best from Query.Farm — smart tips, powerful tools, and project updates sent directly to your inbox, but only when we’ve got something great to share.