JSON

public.eql_v3_json_search is EQL's searchable encrypted JSON document type, built on structured encryption (ste_vec). The document is encrypted as a vector of encrypted entries — one entry per path inside the document — and every path is queryable without decryption: containment, field and array access, exact field matching, and range comparisons on extracted leaves.

Like every EQL type, it holds ciphertext the database can't read. Encryption, decryption, and selector generation happen in the client — the Stack SDK or CipherStash Proxy. See Searchable encryption for how querying ciphertext works at all.

The types

Four jsonb-backed domains make up the encrypted JSON surface. The family follows the same convention as the scalars: the bare name is storage-only, and a suffix adds capability.

Payload shape

An encrypted JSON document uses a different payload shape from the scalar types: the standard envelope keys are present (v, i, plus the k: "sv" discriminator — envelope anatomy is covered in Core concepts), but there is no root ciphertext — the root document's ciphertext is the root sv entry. Two document-level keys plus the vector:

Each entry has:

The decoded op value starts with a domain-tag byte (0x00 numeric, 0x01 string) followed by the CLLW ciphertext, so numeric and string values in one column keep a consistent total order. Pre-release payloads named this key oc and backed it with CLLW ORE, and older ones split it further, into ocf (fixed-width, numeric) and ocv (variable-width, string).

A document payload for a public.eql_v3_json_search column:

{
  "v": 3,
  "k": "sv",
  "i": { "t": "orders", "c": "metadata" },
  "h": "a4f1c2...",
  "sv": [
    { "s": "2517068c0d1f9d4d41d2c666211f785e", "c": "mBbKmM..." },
    { "s": "f510853a4ab9d4f75f51a533ac264c5d", "c": "mBbKmQ...", "op": "01a3f2..." },
    { "s": "33743aed3ae636f6bf05cff11ac4b519", "c": "mBbKmR...", "op": "004e19..." }
  ]
}

• First entry: an object root — no ordering term
• Second entry: a string leaf — op starting with tag 01
• Third entry: a numeric leaf — op starting with tag 00

A containment query payload (eql_v3.query_json) has the same sv shape but its entries carry no c — containment matches selectors, never ciphertexts. This is the needle the client builds for a @> query:

{
  "sv": [
    { "s": "f510853a4ab9d4f75f51a533ac264c5d", "op": "01a3f2..." }
  ]
}

Storing encrypted JSON

Type the column as public.eql_v3_json_search:

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  metadata public.eql_v3_json_search
);

There is no database-side configuration step. Which selectors and terms a document carries is decided by the encryption client; typing the column as public.eql_v3_json_search is what makes the encrypted operators and functions resolve. The domain's CHECK constraint validates the payload shape on insert, so malformed values are rejected at write time.

For a document you never search, type the column as the storage-only public.eql_v3_json instead: it takes the plain {v, i, c} envelope, rejects a ste_vec document, and blocks every comparison operator — the JSON analogue of public.eql_v3_boolean.

Insert and read through the Stack SDK or Proxy, which encrypt the document into the ste_vec payload on write and decrypt it on read.

What each node type supports

During encryption, the client flattens the document: each unique path gets a deterministic selector hash. Each node then emits a path entry (selector over the path, carrying the ordering term where the type has one) and a value entry, whose selector covers the path and the canonical value:

Exact matching is the presence of a value selector in the stored document, which makes it injective and loss-free for every type — including text, bigint and numeric, where the old ordering-term comparison collided ("café" with "cafe", 9007199254740993 with …992). It is expressed as document containment, not as = on an extracted leaf:

SELECT * FROM orders WHERE metadata @> $1::eql_v3.query_json;

JSON null here means a null literal inside the document. A SQL NULL column value is not encrypted at all.

Blocked native jsonb operators

These native PostgreSQL jsonb operators are blocked on public.eql_v3_json_search. They raise an error rather than silently running plaintext-jsonb semantics against the encrypted payload:

• Key/path existence: ?, ?|, ?&, @?, @@
• Path extraction: #>, #>>
• Mutation: -, #-, ||
• Root-document comparison: =, <>, <, <=, >, >=

Equality on the extract surface raises too: col -> 'sel'::text = $1::… is a blocker for every family, because an extracted path entry carries no value selector and so cannot answer an exact match. Use value-selector containment for that.

Use containment (@> / <@), field access (-> / ->>), or the eql_v3.jsonb_path_* functions instead. There is no server-side mutation of an encrypted document — updates re-encrypt in the client.

Functions

Every JSON query addresses paths by selector hash — the deterministic identifier the client emits for a JSON path during encryption, not a plaintext path like $.customer.tier. Operands must be typed, or PostgreSQL resolves the native jsonb operator instead of the encrypted one.

The examples below all query one encrypted metadata document. In plaintext:

{
  "customer": {
    "tier": "premium",
    "region": "apac"
  },
  "total": 149.95,
  "items": ["sku-1042", "sku-2210"]
}

The *_selector placeholders stand for the selector hash of each path: region_selector for $.customer.region, total_selector for $.total, and items_selector for $.items.

Containment and exact matching

@> tests whether the encrypted document contains a structure; <@ is the reverse. Build the needle in the client and cast it to eql_v3.query_json; eql_v3.ste_vec_contains(a, b) is the function form. This is both the structural-containment operator and the way to match a field exactly — the client emits a value-selector needle for the field and value you're matching.

SELECT * FROM orders
WHERE metadata @> $1::eql_v3.query_json;

For large tables, back containment with a GIN index. The typed @> inlines to a native jsonb @> over eql_v3.to_ste_vec_query(col)::jsonb, so a GIN index on that same expression engages:

CREATE INDEX orders_metadata_gin
  ON orders USING gin (eql_v3.to_ste_vec_query(metadata)::jsonb jsonb_path_ops);

Field access

-> returns a public.eql_v3_json_entry; ->> serializes that entry as ciphertext text. Fields are addressed by selector hash, and array elements by 0-based index.

SELECT metadata -> 'selector_hash'::text  FROM orders;  -- entry
SELECT metadata ->> 'selector_hash'::text FROM orders;  -- entry as ciphertext text
SELECT metadata -> 0 FROM orders;                       -- array element by index

An extracted public.eql_v3_json_entry is ordered-comparable, but not equality-comparable: = / <> on it raise.

eql_v3.ord_term

Range comparisons and ORDER BY on an extracted String or Number leaf, via eql_v3.ord_term.

SELECT * FROM orders
WHERE (metadata -> 'total_selector'::text) > $1::public.eql_v3_json_entry;

A btree on eql_v3.ord_term(col -> '<selector>'::text) engages range and ORDER BY; the GIN index on eql_v3.to_ste_vec_query(col)::jsonb engages exact matching. See Indexes.

eql_v3.min / max

MIN / MAX over an extracted ordered leaf.

SELECT eql_v3.min(metadata -> 'total_selector'::text) FROM orders;

eql_v3.jsonb_path_query

Path queries take the same selector hashes. jsonb_path_query returns every matching entry, jsonb_path_query_first the first, and jsonb_path_exists a boolean.

SELECT eql_v3.jsonb_path_query(metadata, 'selector_hash')       FROM orders;  -- all matches
SELECT eql_v3.jsonb_path_query_first(metadata, 'selector_hash') FROM orders;  -- first match
SELECT eql_v3.jsonb_path_exists(metadata, 'selector_hash')      FROM orders;  -- boolean

eql_v3.jsonb_array_*

Helpers over an encrypted array node. jsonb_array_elements yields encrypted entries — each carrying the grafted key header, which makes it the decryptable unit.

SELECT eql_v3.jsonb_array_length(metadata -> 'items_selector'::text)   FROM orders;
SELECT eql_v3.jsonb_array_elements(metadata -> 'items_selector'::text) FROM orders;

eql_v3.jsonb_array_elements_text, which streamed each element as bare ciphertext text, was removed in EQL 3.0.1: an entry's ciphertext is no longer independently decryptable, so the rows it produced were useless. Use eql_v3.jsonb_array_elements.

Worked example

An orders table with an encrypted metadata document. The plaintext your application works with:

{
  "customer": {
    "tier": "premium",
    "region": "apac"
  },
  "items": ["sku-1042", "sku-2210"]
}

The client encrypts this into a ste_vec payload with selectors for $, $.customer, $.customer.tier, $.customer.region, $.items, and each array element — every path becomes queryable.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  metadata public.eql_v3_json_search
);

INSERT INTO orders (metadata) VALUES ($1);
-- $1 is the encrypted ste_vec payload produced by the Stack SDK or Proxy

SELECT id FROM orders
WHERE metadata @> $1::eql_v3.query_json;

SELECT id FROM orders
WHERE eql_v3.jsonb_path_exists(metadata, 'region_selector')
  AND (metadata -> 'total_selector'::text) > $1::public.eql_v3_json_entry;

Where to next