JSON

JSON and SQL/JSON in MemCP

MemCP provides native JSON and SQL/JSON support on its high-performance columnar SQL engine. JSON documents can be filtered, joined, grouped, sorted, modified and assembled into deeply nested application responses without leaving SQL.

The same JSON engine is exposed through both MemCP SQL dialects: applications get MySQL/MariaDB-compatible JSON functions such as JSON_EXTRACT, JSON_VALUE, JSON_OBJECT, JSON_ARRAYAGG and JSON_TABLE, as well as PostgreSQL-compatible json/jsonb operators, constructors, aggregates and JSONPath functions. This makes it possible to migrate JSON-heavy SQL queries or run MemCP beside an existing MySQL or PostgreSQL application. See Migration from MySQL and PostgreSQL for connection options and Supported SQL for the wider SQL feature set.

JSON columns validate incoming documents and convert them to MemCP's native tagged BSON value. Objects and arrays therefore remain structured while they are filtered, joined, sorted or aggregated; they are serialized to JSON only at the SQL/API boundary.

JSON functions also accept ordinary character strings containing JSON. Such strings are parsed when the function needs a JSON value. Use a JSON or JSONB column for repeatedly queried documents so that parsing happens on write instead of on every read.

MemCP compared with MySQL JSON, MariaDB JSON and PostgreSQL JSONB

SQL dialect	Familiar JSON syntax in MemCP	Internal representation in MemCP	Typical use
MySQL	JSON_EXTRACT, JSON_VALUE, ->, ->>, JSON_TABLE	Native immutable BSON	Existing MySQL applications and document columns
MariaDB	MySQL-style JSON functions and JSONPath	Native immutable BSON instead of LONGTEXT	MariaDB-compatible SQL and mixed relational/document workloads
PostgreSQL	json/jsonb casts, operators, JSONPath and set-returning functions	One native BSON representation for both json and jsonb	PostgreSQL JSONB queries, containment and relational projection

Unlike a database that reparses a text document for every JSON expression, a declared MemCP JSON or JSONB column is converted when it is written. Frequently used paths can participate in deterministic computed expressions and computed indexes.

Quick start

CREATE TABLE events (
	id INT PRIMARY KEY,
	category VARCHAR(40),
	payload JSON,
	created_at DATETIME
);

INSERT INTO events VALUES
	(1, 'customer', '{"customer":{"name":"Ada","rank":2},"tags":["sql","go"]}', NOW());

SELECT
	id,
	JSON_VALUE(payload, '$.customer.name') AS customer,
	JSON_EXTRACT(payload, '$.customer.rank') + 1 AS next_rank
FROM events
WHERE JSON_CONTAINS(payload, '"sql"', '$.tags')
ORDER BY JSON_VALUE(payload, '$.customer.rank' RETURNING SIGNED);

JSON scalars participate in normal SQL coercion. A numeric result of JSON_EXTRACT, for example, can be added, compared and sorted numerically. NULL in SQL and the JSON literal null remain distinct where the selected dialect distinguishes them.

Common MySQL JSON and SQL JSON queries

Extract a value from JSON in SQL

SELECT JSON_EXTRACT(payload, '$.customer.rank') AS rank
FROM events;

SELECT payload->>'$.customer.name' AS customer_name
FROM events;

Filter SQL rows by a JSON attribute

SELECT id
FROM events
WHERE JSON_VALUE(payload, '$.customer.name') = 'Ada'
	AND JSON_CONTAINS(payload, '"sql"', '$.tags');

Order SQL results by a JSON value

SELECT id, payload
FROM events
ORDER BY JSON_VALUE(payload, '$.customer.rank' RETURNING SIGNED), id;

The numeric RETURNING type prevents lexical ordering such as 1, 10, 2. Eligible deterministic path expressions can be backed by a computed index.

Aggregate SQL rows into a JSON array

SELECT category, JSON_ARRAYAGG(
	JSON_OBJECT('id', id, 'payload', payload)
) AS documents
FROM events
GROUP BY category;

Convert a JSON array into SQL rows

SELECT item.ord, item.name
FROM JSON_TABLE(
	'[{"name":"Ada"},{"name":"Bob"}]',
	'$[*]' COLUMNS (
		ord FOR ORDINALITY,
		name TEXT PATH '$.name'
	)
) AS item;

MySQL and MariaDB syntax

MemCP implements the following MySQL/MariaDB-style JSON surface:

Area	Functions and operators
Construction and aggregation	JSON_ARRAY, JSON_OBJECT, JSON_ARRAYAGG, JSON_OBJECTAGG
Inspection and extraction	JSON_EXTRACT, JSON_VALUE, JSON_KEYS, JSON_LENGTH, JSON_DEPTH, JSON_TYPE, JSON_VALID, ->, ->>
Search and comparison	JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_OVERLAPS, JSON_SEARCH, MEMBER OF
Modification	JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_MERGE_PATCH, JSON_MERGE_PRESERVE, JSON_MERGE
Conversion and formatting	JSON_QUOTE, JSON_UNQUOTE, JSON_PRETTY, JSON_STORAGE_SIZE, JSON_STORAGE_FREE
Validation	JSON_SCHEMA_VALID, JSON_SCHEMA_VALIDATION_REPORT
Relational projection	JSON_TABLE, including path columns and FOR ORDINALITY

Paths start at $. Object members, zero-based array indexes, wildcards, recursive descent and last/last-N array indexes are supported.

SELECT JSON_EXTRACT(
	'{"orders":[{"total":10},{"total":25}]}',
	'$.orders[last].total'
); -- 25

SELECT *
FROM JSON_TABLE(
	'[{"name":"Ada"},{"name":"Bob"}]',
	'$[*]' COLUMNS (
		ord FOR ORDINALITY,
		name TEXT PATH '$.name'
	)
) AS people;

The upstream references are MariaDB's JSON functions index and JSONPath reference. MariaDB adds functions over time; the table above is the supported MemCP surface, not a claim that every function in every MariaDB release is available.

PostgreSQL syntax

The PostgreSQL-compatible parser accepts both json and jsonb casts and column declarations. Internally both use the same native BSON representation.

Area	Functions and operators
Extraction	->, ->>, #>, #>>, json_extract_path, jsonb_extract_path, and their _text variants
jsonb operators	, ?&,	, -, #-, @?, @@
Construction and conversion	json, to_json, to_jsonb, array_to_json, row_to_json, json_build_array, jsonb_build_array, json_build_object, jsonb_build_object, json_object, jsonb_object, json_scalar, json_serialize
Inspection and modification	json_array_length, jsonb_array_length, json_typeof, jsonb_typeof, jsonb_set, jsonb_set_lax, jsonb_insert, json_strip_nulls, jsonb_strip_nulls, jsonb_pretty
Set-returning functions	json[b]_array_elements[_text], json[b]_each[_text], json[b]_object_keys, json[b]_populate_record[set], jsonb_populate_record_valid, json[b]_to_record[set]
JSONPath	jsonb_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array, jsonb_path_query_first, plus their _tz variants
Aggregation	json_agg, jsonb_agg, strict variants, json_object_agg/jsonb_object_agg and their _strict, _unique and _unique_strict variants
SQL/JSON	JSON_ARRAY, JSON_OBJECT, JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_EXISTS, JSON_QUERY, JSON_VALUE, JSON_TABLE

SELECT
	payload->'customer'->>'name' AS name,
	payload @> '{"tags":["sql"]}'::jsonb AS has_sql
FROM events
ORDER BY payload->'customer'->'rank';

SELECT jsonb_path_query_array(
	'{"values":[1,2,3,4]}'::jsonb,
	'$.values[*] ? (@ > 2)'
); -- [3,4]

See PostgreSQL's official JSON functions and operators and aggregate functions documentation for the source syntax and semantics. The tables above identify the forms currently accepted by MemCP.

Building nested application documents

JSON constructors and aggregates compose with correlated subqueries. This allows a relational schema to emit complete API documents, including arrays nested several levels deep:

SELECT JSON_OBJECT(
	'id', delivery_note.id,
	'number', delivery_note.note_number,
	'date', delivery_note.delivery_date,
	'items', (
		SELECT JSON_ARRAYAGG(JSON_OBJECT(
			'id', delivery_item.id,
			'sku', delivery_item.sku,
			'quantity', delivery_item.quantity,
			'serialNumbers', (
				SELECT JSON_ARRAYAGG(JSON_OBJECT(
					'id', serial_number.id,
					'value', serial_number.serial_number
				))
				FROM delivery_serial_numbers AS serial_number
				WHERE serial_number.delivery_item_id = delivery_item.id
			)
		))
		FROM delivery_items AS delivery_item
		WHERE delivery_item.delivery_note_id = delivery_note.id
	)
) AS document
FROM delivery_notes AS delivery_note
ORDER BY delivery_note.delivery_date DESC;

JSON_ARRAYAGG collects values first and emits one exact-sized BSON array at aggregate finalization. Nested aggregation therefore avoids repeatedly copying a growing JSON string. The same finalization is applied independently to scalar subqueries, groups and set-operation branches.

Storage and indexing

• A declared JSON or JSONB column rejects invalid documents on INSERT and UPDATE.
• MemCP stores one native BSON value, not both a JSON string and a parsed tree. BSON is a normal tagged SCM value and needs no JSON-specific storage engine.
• Serialization is canonical rather than lexical: insignificant whitespace is not retained, object key order is deterministic, and duplicate object keys do not remain separate.
• JSON extraction expressions can be used in WHERE, GROUP BY and ORDER BY. Eligible deterministic expressions can be materialized as computed columns and served by computed indexes, so document-style tables do not require reparsing every row for each query.

For frequently filtered or sorted attributes, keep the JSON payload for flexibility and expose the hot path as a deterministic expression used consistently by queries. Fully relational columns remain preferable for keys, high-selectivity joins and attributes with strong schema constraints.

Compatibility notes

MemCP aims at practical query compatibility, but it does not preserve PostgreSQL's textual json representation separately from jsonb, nor MariaDB's LONGTEXT representation. Native BSON is used for both dialects. Applications that depend on original whitespace, duplicate-key preservation or byte-for-byte round trips should store the original document in a separate text column.

Unknown or newly introduced upstream JSON functions should be treated as unsupported until they appear in the supported tables above and in MemCP's compatibility tests.

Frequently asked questions about SQL JSON in MemCP

Does MemCP support MySQL JSON functions?

Yes. MemCP supports commonly used MySQL JSON functions and operators for construction, extraction, search, modification, aggregation, schema validation and JSON_TABLE. The complete currently supported surface is listed under #MySQL and MariaDB syntax.

Can MemCP run JSON_EXTRACT and JSON_VALUE on a string?

Yes. Both functions accept valid JSON text. A declared JSON column is more efficient for repeated queries because MemCP validates and converts the document when it is written.

Does MemCP support PostgreSQL JSONB?

Yes. The PostgreSQL-compatible parser supports json/jsonb casts, extraction and containment operators, JSONPath, constructors, aggregates and set-returning JSON functions. MemCP represents both SQL types with the same immutable BSON value internally.

Can MemCP index a field inside a JSON document?

JSON extraction can be used as a deterministic computed expression. When eligible, MemCP can materialize that expression and use a computed index for filters or ordering by the extracted JSON attribute.

Should all application data be stored in one JSON column?

It is possible to use a document-style table containing an ID, timestamps and a JSON payload. Relational columns are still preferable for primary keys, frequently joined keys and strongly constrained attributes. A hybrid schema normally gives the optimizer more options while retaining JSON flexibility.