Checking field type and path existence

This recipe shows how a JSON index is used to check document structure: the presence of a specific path, the type of value at the path, etc. These tasks are common when working with heterogeneous JSON documents where some fields are optional or may have different types.

Prerequisites

CREATE TABLE documents (
    id Uint64,
    payload JsonDocument,
    PRIMARY KEY (id),
    INDEX json_idx GLOBAL USING json ON (payload)
);

UPSERT INTO documents (id, payload) VALUES
    (1, JsonDocument(@@{"archived": false, "value": 1,    "data": [1, 2, 3]}@@)),
    (2, JsonDocument(@@{"archived": false, "value": 2,    "data": "plain text"}@@)),
    (3, JsonDocument(@@{"archived": true,  "value": 3,    "data": {"nested": true}}@@)),
    (4, JsonDocument(@@{"archived": false, "value": null, "meta": "no data field"}@@));

Find documents where a field contains an array

The JsonPath method .type() returns a string name of the value type at the specified path. This allows filtering documents by content type:

SELECT id
FROM documents VIEW json_idx
WHERE JSON_VALUE(payload, '$.data.type()' RETURNING Utf8) = "array"u;

The path token $.data is added to the index — the method .type() completes the path token construction. The exact check of the string value "array" is performed by a post-filter.

Result:

id
1

The full list of values returned by the method .type() is given in the JsonPath syntax description.

Find documents where a field is a non-empty array

The method .size() returns the number of array elements (or 1 for a scalar, 0 for a missing path). In combination with a type filter:

SELECT id
FROM documents VIEW json_idx
WHERE JSON_VALUE(payload, '$.data.type()' RETURNING Utf8) = "array"u
  AND JSON_VALUE(payload, '$.data.size()' RETURNING Int64) > 0;

Both fragments are indexed by the corresponding paths, and the exact values are checked by a post-filter. Result: id = 1.

Find documents with value false or null

To check for "value equals false" or "value equals null", use JsonPath inside JSON_EXISTS, not JSON_VALUE(...) IS NULL:

-- The value of the 'archived' field is false
SELECT id
FROM documents VIEW json_idx
WHERE JSON_EXISTS(payload, '$.archived ? (@ == false)');

-- The value of the 'value' field is null
SELECT id
FROM documents VIEW json_idx
WHERE JSON_EXISTS(payload, '$.value ? (@ == null)');

The index search operation includes the path $.archived (or $.value) and the values false or null, respectively.

See also

• JSON_EXISTS — what is allowed in JsonPath expressions.
• JSON_VALUE — what is allowed when extracting values.
• JsonPath: methods — list of methods (type, size, keyvalue, ...) and JsonPath predicates.