Implementing JSON Value Existence Checks in SQLite: Workarounds and Proposals

JSON Value Existence Challenges in SQLite’s JSON1 Extension

The absence of a native json_contains-style function in SQLite’s JSON1 extension forces developers to use suboptimal workarounds when checking for values in JSON arrays/objects. While JSON1 provides foundational tools like json_extract, json_each, and json_array_length, it lacks a direct method to test whether a JSON structure contains a specific scalar value, key, or element. This gap becomes apparent when comparing SQLite’s JSON capabilities to other database systems (e.g., PostgreSQL’s @> operator) or programming paradigms (Python’s in keyword). The discussion highlights three critical pain points:

1. Verbose Syntax for Simple Checks: Queries requiring existence checks demand convoluted joins with json_each or recursive CTEs even for trivial operations like "is value X present in array Y?"
2. Performance Overheads in CRUD Operations: Using json_each in UPDATE/DELETE statements often necessitates subqueries or temporary tables, adding computational overhead.
3. Inconsistency with JSON Path Semantics: Existing functions like json_type and json_valid don’t address membership testing, creating asymmetry in JSON manipulation capabilities.

For example, deleting records where a JSON array contains 'list value' requires a subquery:

DELETE FROM example WHERE ID IN (
  SELECT ID FROM example, json_each(example.Data) 
  WHERE json_each.value = 'list value'
);

A native json_contains(Data, '$', 'list value') would simplify this to a single WHERE clause, eliminating the subquery and improving readability. The lack of such a function pushes developers toward user-defined functions (UDFs), virtual tables, or external extensions, fragmenting codebases and complicating maintenance.

Root Causes of JSON Membership Testing Limitations

1. JSON1 Extension’s Design Philosophy

SQLite’s JSON1 extension prioritizes minimalism and compatibility with RFC-8259 rather than feature parity with NoSQL databases. Functions like json_each expose JSON structures relationally but avoid higher-level abstractions like membership operators. This design choice keeps the extension lightweight but shifts complexity to query authors.

2. Path vs. Value Query Asymmetry

While json_extract allows retrieving values at specific paths (e.g., $.items[3].id), there’s no inverse function to determine which paths contain a given value. This asymmetry forces developers to materialize the entire JSON structure via json_each and scan it procedurally.

3. SQLite’s Static Function Registry

SQLite’s built-in functions are compiled into the core library or loadable extensions. Adding functions like json_contains requires modifying the JSON1 extension’s C code or creating a separate extension, which isn’t feasible for many users. Consequently, projects resort to UDFs in languages like Python or Go, introducing foreign function interfaces (FFIs) that degrade performance and complicate deployment.

4. Ambiguity in JSON Containment Semantics

Implementing a universal json_contains requires resolving ambiguities:

• Should it check for value existence in arrays, key existence in objects, or both?
• How should it handle mixed-type JSON values (e.g., numeric 2 vs. string '2')?
• What’s the expected behavior for nested structures (e.g., arrays within objects within arrays)?

The forum proposal suggests dual behavior: checking array elements or object keys depending on the JSON type at the specified path. However, this requires type introspection at runtime, which isn’t free computationally.

Solutions for JSON Value Existence Testing in SQLite

Workaround 1: Leveraging json_each with Joins

For SELECT queries, flatten JSON arrays/objects using json_each in a CROSS JOIN:

SELECT ID FROM example 
CROSS JOIN json_each(example.Data) AS je
WHERE je.value = 'desired_value';

Pros:

• Uses built-in functions; no external dependencies.
• Works for shallow JSON structures.

Cons:

• Fails for nested JSON (pathing not supported in json_each without recursive CTEs).
• Requires materializing the entire JSON structure, causing performance issues with large documents.
• Unusable in UPDATE/DELETE without subqueries.

Workaround 2: Virtual Tables via statement-vtab Extension

Bjoern Hoehrmann’s approach uses the statement-vtab extension to create a virtual table that wraps an existence check subquery:

CREATE VIRTUAL TABLE JSON_SET_CONTAINS USING statement((
  SELECT EXISTS(SELECT 1 FROM JSON_EACH(?1) WHERE value = ?2) AS value
));

Query with:

SELECT ID FROM example 
WHERE (SELECT value FROM JSON_SET_CONTAINS(Data, 'desired_value'));

Pros:

• Encapsulates the existence check into a reusable virtual table.
• Avoids repetitive subqueries in complex statements.

Cons:

• Relies on an external extension not bundled with SQLite.
• Requires initial setup and compatibility checks.

Workaround 3: User-Defined Functions (UDFs)

Implement a custom json_contains function in a host language (e.g., Python’s sqlite3 module):

def json_contains(json_str, path, target_value):
    data = json.loads(json_str)
    # Implement path resolution and containment check
    return 1 if found else 0

connection.create_function("json_contains", 3, json_contains)

Pros:

• Full control over JSON parsing and containment logic.
• Can handle complex pathing and nested structures.

Cons:

• FFI overhead degrades performance, especially with large datasets.
• Requires application-level code, making it unsuitable for standalone SQLite use.

Workaround 4: Custom JSON1 Extension in C

For maximum performance, extend SQLite’s JSON1 C code with a json_contains function. The core logic would:

1. Parse the input JSON.
2. Resolve the JSON path (e.g., $.items).
3. Check if the resolved value is an array/object.
4. Search for the target value in keys (objects) or elements (arrays).

Sample implementation sketch:

static void jsonContainsFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
) {
  JsonParse *pParse; /* JSON parse tree */
  JsonNode *pNode;   /* Node at specified path */
  int contains = 0;

  /* Parse JSON input */
  if( jsonParse(ctx, argv[0], &pParse) ) return;

  /* Resolve JSON path */
  pNode = jsonLookup(pParse, argv[1], 0);
  if( !pNode ) {
    sqlite3_result_null(ctx);
    goto exit;
  }

  /* Check array/object type */
  if( pNode->eType == JSON_ARRAY ) {
    for(int i=0; i<pNode->n; i++){
      if( jsonCompare(&pNode->u.apCell[i], argv[2]) ){
        contains = 1;
        break;
      }
    }
  } else if( pNode->eType == JSON_OBJECT ) {
    for(int i=0; i<pNode->n; i++){
      if( strcmp(pNode->u.apCell[i].u.zKey, sqlite3_value_text(argv[2])) == 0 ){
        contains = 1;
        break;
      }
    }
  }

  sqlite3_result_int(ctx, contains);
exit:
  jsonParseFree(pParse);
}

Pros:

• Native performance; integrates directly with JSON1’s optimized parser.
• Supports all JSON path expressions available in SQLite.

Cons:

• Requires C proficiency and rebuilding SQLite/extensions.
• Maintenance burden if JSON1 internals change.

Proposal: Native json_contains Function

The ideal solution is adding json_contains to the JSON1 extension. Key requirements:

1. Signature: json_contains(json, path, value) -> 0/1/NULL
2. Path Resolution: Uses existing JSON path semantics (same as json_extract).
3. Type-Aware Search:
   • Arrays: Check if value exists as an element.
   • Objects: Check if value exists as a key.
   • Other types: Return 0 (e.g., searching for a key in a JSON string).
4. Strict Type Checking: Differentiate between JSON types (e.g., 2 ≠ '2').

Implementation Considerations:

• Recursive Search: Optionally support ** wildcard paths for deep searches.
• Collation Sequences: Respect SQLite’s collation for string comparisons.
• Error Handling: Return NULL for invalid JSON/path inputs (consistent with json_extract).

Impact:

• Simplifies queries, especially in CRUD operations.
• Reduces the need for UDFs and external extensions.
• Aligns SQLite’s JSON support with developer expectations from other languages/databases.

Final Recommendation: For most users, Workaround 2 (virtual tables) offers a balance of convenience and performance without C extensions. However, projects requiring maximum performance and JSON complexity should consider a custom JSON1 extension. Advocating for a native json_contains via SQLite’s GitHub issue tracker or mailing list could accelerate its inclusion in future releases.