SQLite TCL ‘exists’ Method Behavior with JSON Queries: Handling Nulls and Missing Keys

Issue Overview: Unexpected Results When Using TCL’s ‘exists’ Method with JSON Queries

When working with SQLite’s JSON1 extension and TCL bindings, developers often use the db exists method to determine whether a query returns at least one row. However, a critical nuance arises when combining this method with json_extract or other JSON functions. The core issue stems from how SQLite handles JSON nulls, missing keys, and the implicit conversion of these results in TCL.

In the example provided, executing db exists {SELECT json_extract('{"a":15,"b":26}','$.c')} returns 1 (true) even though the JSON key c does not exist in the object. This contradicts the expectation that a missing key should be treated as non-existent. The confusion intensifies when comparing this behavior to db eval, which returns {} (an empty string in TCL) for the same query. The root of the problem lies in the interaction between three components:

1. SQLite’s JSON1 Extension: The json_extract function returns NULL for non-existent keys or JSON nulls.
2. TCL’s SQLite Bindings: The db exists method checks for the presence of at least one row in the result set, regardless of column values.
3. Implicit Type Conversions: TCL converts SQL NULL to an empty string ({}) in scalar contexts, creating ambiguity between actual empty strings and missing data.

This behavior becomes problematic when developers assume db exists inspects the value of the extracted JSON field rather than the mere existence of a row. For instance, json_extract('{"a":15,"b":26}','$.c') returns NULL in SQL, which TCL interprets as {}. Since the query still produces one row (with a NULL value), db exists returns 1. This leads to false positives when checking for the existence of JSON keys or properties.

Possible Causes: JSON Null Semantics, TCL Bindings, and Row-Counting Logic

1. JSON Null vs. SQL Null vs. Missing Key Ambiguity

The JSON1 extension differentiates between explicit JSON null values and keys that do not exist. However, when using json_extract, both scenarios return SQL NULL by default. For example:

• json_extract('{"a":null}','$.a') returns NULL (JSON null).
• json_extract('{"a":5}','$.b') returns NULL (missing key).

This conflation means that SQL queries cannot distinguish between a key with a null value and a missing key unless additional filtering is applied. The db exists method inherits this ambiguity because it operates at the row level, not the value level.

2. TCL’s ‘db exists’ Method and Row Presence

The db exists method in TCL’s SQLite binding executes the provided SQL statement and returns 1 if the result set contains at least one row, regardless of the contents of that row. It does not evaluate whether specific columns in the row are NULL or non-empty. This design aligns with SQL’s EXISTS() operator but conflicts with intuitive expectations when working with semi-structured data like JSON.

For example, the query SELECT NULL produces one row with a NULL value. Consequently, db exists {SELECT NULL} returns 1, even though the value itself is NULL. This behavior directly impacts JSON queries where json_extract returns NULL for missing keys.

3. Implicit TCL-to-SQLite Type Conversions

TCL converts SQL NULL values to empty strings ({}) in scalar contexts. This conversion obscures the distinction between:

• A JSON key that exists with an empty string value (""),
• A JSON key that exists with a null value,
• A JSON key that does not exist.

All three cases may surface as {} in TCL, making it impossible to differentiate them without additional SQL logic.

Troubleshooting Steps, Solutions & Fixes: Disambiguating JSON Key Existence with SQL Filters

1. Explicitly Filtering for Non-Null Values

To ensure db exists returns 0 for missing JSON keys, modify the SQL query to exclude rows where the extracted value is NULL:

db exists {SELECT json_extract('{"a":15,"b":26}','$.c') AS c WHERE c IS NOT NULL}

This query aliases the extracted value as c and applies a WHERE clause to filter out NULL values. If the key $.c is missing, json_extract returns NULL, causing the WHERE clause to exclude the row. With no rows in the result set, db exists returns 0.

2. Using JSON_EXISTS() for Key Presence Checks

SQLite 3.40.0+ introduces the json_exists function, which explicitly checks for the existence of a JSON path in a JSON string. For example:

SELECT json_exists('{"a":15,"b":26}','$.c') -- Returns 0 (false)

Use this function in combination with db exists:

db exists {SELECT 1 WHERE json_exists('{"a":15,"b":26}','$.c')}

This approach bypasses the ambiguity of NULL values by directly testing the presence of the JSON path.

3. Leveraging JSON_TYPE to Distinguish Nulls and Missing Keys

The json_type function returns the type of a JSON element at a specified path. For missing keys, it returns null (as a string). For explicit JSON nulls, it returns null (as a string). While this does not fully resolve the ambiguity, it allows for more granular control:

db exists {
  SELECT 1 
  WHERE json_type('{"a":15,"b":26}','$.c') IS NOT NULL
}

This query returns 0 because json_type returns null (string) for missing keys, which the IS NOT NULL clause filters out.

4. Combining Multiple Conditions for Precision

For scenarios requiring strict validation (e.g., checking that a key exists and has a non-null value), combine json_extract with json_type:

db exists {
  SELECT 1 
  WHERE json_extract('{"a":15,"b":26}','$.c') IS NOT NULL
  AND json_type('{"a":15,"b":26}','$.c') != 'null'
}

This ensures that the key exists and its value is not a JSON null.

5. Normalizing JSON Data in Temporary Tables

For complex JSON structures, materialize the JSON data into a temporary table with explicit columns. This separates key existence checks from JSON parsing:

db eval {
  CREATE TEMP TABLE tmp_json AS 
  SELECT key, value 
  FROM json_each('{"a":15,"b":26}');
}
db exists {SELECT 1 FROM tmp_json WHERE key = 'c'}

This method leverages SQLite’s json_each function to flatten the JSON object into a table, enabling traditional SQL queries for key existence.

6. Avoiding db exists for JSON Value Checks

In some cases, replacing db exists with db eval followed by TCL-level checks provides clearer logic:

set value [db eval {SELECT json_extract('{"a":15,"b":26}','$.c')}]
if {$value ne ""} {
  puts "Key exists with value $value"
} else {
  puts "Key does not exist or is null"
}

While this increases code complexity, it explicitly handles the NULL-to-empty-string conversion in TCL.

7. Version-Specific Workarounds for Older SQLite Instances

For SQLite versions prior to 3.40.0 (lacking json_exists), emulate key existence checks using json_valid and error handling:

proc json_key_exists {json_data path} {
  set result [db eval "SELECT json_extract(:json_data, :path)"]
  return [expr {$result ne ""}]
}

This procedure assumes that non-empty strings indicate valid values, which may not hold true for all cases but works for primitive types.

By understanding the interplay between SQLite’s JSON functions, TCL’s type conversions, and the row-counting logic of db exists, developers can craft precise queries that disambiguate missing keys, JSON nulls, and valid values. The solutions outlined above provide a toolkit for adapting to specific use cases, ensuring accurate JSON key existence checks in TCL applications.