Resolving “no such function: json_each” Error in SQLite JSON Queries

Issue Overview: Misuse of json_each and JSON1 Extension Configuration

The error "no such function: json_each" occurs when attempting to query JSON arrays or objects using SQLite’s JSON1 extension. This error reflects one of two fundamental issues: (1) improper syntax when invoking the json_each table-valued function or (2) misconfiguration of the JSON1 extension in the SQLite environment.

Functional Context of json_each

The json_each function is a table-valued function designed to iterate over elements of a JSON array or key-value pairs of a JSON object. Unlike scalar functions (e.g., json_array(), json_insert()), which return single values, table-valued functions generate virtual tables that must be referenced in the FROM clause of a query. Attempting to use json_each in the SELECT clause or WHERE clause without proper context will trigger a "no such function" error because SQLite interprets it as a scalar function call, which does not exist.

JSON1 Extension Availability

The JSON1 extension is included by default in SQLite versions 3.38.0 (2022-02-22) and later. For earlier versions, such as SQLite 3.31.1 (reported in the error), JSON1 must be explicitly enabled during compilation or dynamically loaded at runtime. Even if JSON1 is available, certain build configurations (e.g., -DSQLITE_OMIT_JSON) or environment restrictions (e.g., restrictive embedded systems) may disable it.

Query Structure and Syntax

The original query attempted to use json_each in the SELECT clause:

SELECT json_each(z.i) FROM z WHERE json_each.value == 2;

This syntax is invalid because json_each generates a virtual table. The correct approach involves joining the output of json_each with the source data in the FROM clause.

Possible Causes: Version Compatibility and Syntax Misapplication

Cause 1: SQLite Version Without JSON1 Support

SQLite versions prior to 3.38.0 do not bundle the JSON1 extension by default. If the environment uses an older version (e.g., 3.31.1), the JSON1 extension might not be available unless explicitly enabled. This leads to missing functions like json_each, json_tree, and json_array().

Cause 2: Improper Placement of json_each in Query

Using json_each outside the FROM clause violates SQLite’s syntax rules for table-valued functions. For example:

-- Invalid: json_each in SELECT clause
SELECT json_each(column) FROM table;

-- Invalid: json_each in WHERE clause without FROM
SELECT * FROM table WHERE json_each.value = 2;

These usages force SQLite to interpret json_each as a scalar function, which does not exist, hence the error.

Cause 3: JSON1 Extension Not Loaded

Even in SQLite versions where JSON1 is available, the extension might not be loaded. For example, in some precompiled binaries or custom builds, extensions like JSON1 are modular and require runtime activation using SELECT load_extension('json1'); or compile-time flags.

Troubleshooting Steps and Solutions: Configuring JSON1 and Correcting Query Syntax

Step 1: Verify SQLite Version and JSON1 Availability

1. Check SQLite version:

   sqlite3 --version
   

   If the version is older than 3.38.0, JSON1 may not be included by default.

2. Test JSON1 functions:

   SELECT json('{}'); -- Returns '{}' if JSON1 is active
   SELECT json_array(1,2); -- Returns '[1,2]'
   

   If these commands fail, JSON1 is either disabled or unavailable.

3. Enable JSON1 dynamically (if supported):

   SELECT load_extension('json1');
   

   Note: This requires the environment to allow dynamic loading (e.g., sqlite3_enable_load_extension() in applications).

Step 2: Correct Query Syntax for json_each

1. Use json_each in the FROM clause:

   WITH
     x(j) AS (SELECT json_array()),
     y(j) AS (SELECT json_insert(j, '$[#]', 1) FROM x),
     z(i) AS (SELECT json_insert(j, '$[#]', 2) FROM y)
   SELECT * 
   FROM z 
   CROSS JOIN json_each(z.i) 
   WHERE json_each.value = 2;
   

   • CROSS JOIN json_each(z.i) generates a virtual table with columns key, value, type, etc.
   • Filter results using WHERE json_each.value = 2.

2. Explicitly reference the virtual table:

   SELECT je.* 
   FROM z, json_each(z.i) AS je 
   WHERE je.value = 2;
   

   Using an alias (je) improves readability.

Step 3: Upgrade SQLite or Rebuild with JSON1

1. Upgrade to SQLite 3.38.0 or later:
   Download the latest precompiled binaries or update via package managers:

   # For Ubuntu/Debian
   sudo apt-get update && sudo apt-get install sqlite3
   

2. Rebuild SQLite with JSON1:

   • Download the SQLite amalgamation source.
   • Compile with:

     gcc -DSQLITE_ENABLE_JSON1 -lsqlite3 sqlite3.c
     

   This ensures JSON1 is statically linked.

Step 4: Validate JSON Structure

Improper JSON formatting can cause silent failures. Use json_valid() to check inputs:

WITH z(i) AS (SELECT json_insert('[1]', '$[#]', 2))
SELECT i, json_valid(i) FROM z;

If json_valid(i) returns 0, the JSON is malformed.

Final Recommendations

1. Standardize SQLite versions across environments to avoid extension mismatches.
2. Use explicit joins with json_each to clarify intent.
3. Prefer modern SQLite builds to leverage built-in JSON1 support.

By addressing version compatibility, syntax errors, and extension configuration, the "no such function: json_each" error can be systematically resolved.