JSON - Revision history

Carli: Created page with "= 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 2026-08-26T07:22:04Z Created page with "= 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 <code>JSON_EXTRACT</code>, <code>JSON_VALUE</code>, <code>JSON_OBJECT</..." New page = 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 <code>JSON_EXTRACT</code>, <code>JSON_VALUE</code>, <code>JSON_OBJECT</code>, <code>JSON_ARRAYAGG</code> and <code>JSON_TABLE</code>, as well as PostgreSQL-compatible <code>json</code>/<code>jsonb</code> 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 <code>JSON</code> or <code>JSONB</code> 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 == {| class="wikitable" ! SQL dialect ! Familiar JSON syntax in MemCP ! Internal representation in MemCP ! Typical use |- | MySQL | <code>JSON_EXTRACT</code>, <code>JSON_VALUE</code>, <code>-></code>, <code>->></code>, <code>JSON_TABLE</code> | 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 | <code>json</code>/<code>jsonb</code> casts, operators, JSONPath and set-returning functions | One native BSON representation for both <code>json</code> and <code>jsonb</code> | PostgreSQL JSONB queries, containment and relational projection |} Unlike a database that reparses a text document for every JSON expression, a declared MemCP <code>JSON</code> or <code>JSONB</code> column is converted when it is written. Frequently used paths can participate in deterministic computed expressions and computed indexes. == Quick start == <pre> 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); </pre> JSON scalars participate in normal SQL coercion. A numeric result of <code>JSON_EXTRACT</code>, for example, can be added, compared and sorted numerically. <code>NULL</code> in SQL and the JSON literal <code>null</code> remain distinct where the selected dialect distinguishes them. == Common MySQL JSON and SQL JSON queries == === Extract a value from JSON in SQL === <pre> SELECT JSON_EXTRACT(payload, '$.customer.rank') AS rank FROM events; SELECT payload->>'$.customer.name' AS customer_name FROM events; </pre> === Filter SQL rows by a JSON attribute === <pre> SELECT id FROM events WHERE JSON_VALUE(payload, '$.customer.name') = 'Ada' AND JSON_CONTAINS(payload, '"sql"', '$.tags'); </pre> === Order SQL results by a JSON value === <pre> SELECT id, payload FROM events ORDER BY JSON_VALUE(payload, '$.customer.rank' RETURNING SIGNED), id; </pre> The numeric <code>RETURNING</code> type prevents lexical ordering such as <code>1, 10, 2</code>. Eligible deterministic path expressions can be backed by a computed index. === Aggregate SQL rows into a JSON array === <pre> SELECT category, JSON_ARRAYAGG( JSON_OBJECT('id', id, 'payload', payload) ) AS documents FROM events GROUP BY category; </pre> === Convert a JSON array into SQL rows === <pre> SELECT item.ord, item.name FROM JSON_TABLE( '[{"name":"Ada"},{"name":"Bob"}]', '$[]' COLUMNS ( ord FOR ORDINALITY, name TEXT PATH '$.name' ) ) AS item; </pre> == MySQL and MariaDB syntax == MemCP implements the following MySQL/MariaDB-style JSON surface: {| class="wikitable" ! Area ! Functions and operators |- | Construction and aggregation | <code>JSON_ARRAY</code>, <code>JSON_OBJECT</code>, <code>JSON_ARRAYAGG</code>, <code>JSON_OBJECTAGG</code> |- | Inspection and extraction | <code>JSON_EXTRACT</code>, <code>JSON_VALUE</code>, <code>JSON_KEYS</code>, <code>JSON_LENGTH</code>, <code>JSON_DEPTH</code>, <code>JSON_TYPE</code>, <code>JSON_VALID</code>, <code>-></code>, <code>->></code> |- | Search and comparison | <code>JSON_CONTAINS</code>, <code>JSON_CONTAINS_PATH</code>, <code>JSON_OVERLAPS</code>, <code>JSON_SEARCH</code>, <code>MEMBER OF</code> |- | Modification | <code>JSON_SET</code>, <code>JSON_INSERT</code>, <code>JSON_REPLACE</code>, <code>JSON_REMOVE</code>, <code>JSON_ARRAY_APPEND</code>, <code>JSON_ARRAY_INSERT</code>, <code>JSON_MERGE_PATCH</code>, <code>JSON_MERGE_PRESERVE</code>, <code>JSON_MERGE</code> |- | Conversion and formatting | <code>JSON_QUOTE</code>, <code>JSON_UNQUOTE</code>, <code>JSON_PRETTY</code>, <code>JSON_STORAGE_SIZE</code>, <code>JSON_STORAGE_FREE</code> |- | Validation | <code>JSON_SCHEMA_VALID</code>, <code>JSON_SCHEMA_VALIDATION_REPORT</code> |- | Relational projection | <code>JSON_TABLE</code>, including path columns and <code>FOR ORDINALITY</code> |} Paths start at <code>$</code>. Object members, zero-based array indexes, wildcards, recursive descent and <code>last</code>/<code>last-N</code> array indexes are supported. <pre> 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; </pre> The upstream references are MariaDB's [https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions JSON functions index] and [https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/jsonpath-expressions 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 <code>json</code> and <code>jsonb</code> casts and column declarations. Internally both use the same native BSON representation. {| class="wikitable" ! Area ! Functions and operators |- | Extraction | <code>-></code>, <code>->></code>, <code>#></code>, <code>#>></code>, <code>json_extract_path</code>, <code>jsonb_extract_path</code>, and their <code>_text</code> variants |- | jsonb operators | <code>@></code>, <code><@</code>, <code>?</code>, <code>?|</code>, <code>?&</code>, <code>||</code>, <code>-</code>, <code>#-</code>, <code>@?</code>, <code>@@</code> |- | Construction and conversion | <code>json</code>, <code>to_json</code>, <code>to_jsonb</code>, <code>array_to_json</code>, <code>row_to_json</code>, <code>json_build_array</code>, <code>jsonb_build_array</code>, <code>json_build_object</code>, <code>jsonb_build_object</code>, <code>json_object</code>, <code>jsonb_object</code>, <code>json_scalar</code>, <code>json_serialize</code> |- | Inspection and modification | <code>json_array_length</code>, <code>jsonb_array_length</code>, <code>json_typeof</code>, <code>jsonb_typeof</code>, <code>jsonb_set</code>, <code>jsonb_set_lax</code>, <code>jsonb_insert</code>, <code>json_strip_nulls</code>, <code>jsonb_strip_nulls</code>, <code>jsonb_pretty</code> |- | Set-returning functions | <code>json[b]_array_elements[_text]</code>, <code>json[b]_each[_text]</code>, <code>json[b]_object_keys</code>, <code>json[b]_populate_record[set]</code>, <code>jsonb_populate_record_valid</code>, <code>json[b]_to_record[set]</code> |- | JSONPath | <code>jsonb_path_exists</code>, <code>jsonb_path_match</code>, <code>jsonb_path_query</code>, <code>jsonb_path_query_array</code>, <code>jsonb_path_query_first</code>, plus their <code>_tz</code> variants |- | Aggregation | <code>json_agg</code>, <code>jsonb_agg</code>, strict variants, <code>json_object_agg</code>/<code>jsonb_object_agg</code> and their <code>_strict</code>, <code>_unique</code> and <code>_unique_strict</code> variants |- | SQL/JSON | <code>JSON_ARRAY</code>, <code>JSON_OBJECT</code>, <code>JSON_ARRAYAGG</code>, <code>JSON_OBJECTAGG</code>, <code>JSON_EXISTS</code>, <code>JSON_QUERY</code>, <code>JSON_VALUE</code>, <code>JSON_TABLE</code> |} <pre> 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] </pre> See PostgreSQL's official [https://www.postgresql.org/docs/current/functions-json.html JSON functions and operators] and [https://www.postgresql.org/docs/current/functions-aggregate.html 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: <pre> 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; </pre> <code>JSON_ARRAYAGG</code> 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 <code>JSON</code> or <code>JSONB</code> column rejects invalid documents on <code>INSERT</code> and <code>UPDATE</code>. * 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 <code>WHERE</code>, <code>GROUP BY</code> and <code>ORDER BY</code>. 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 <code>json</code> representation separately from <code>jsonb</code>, 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 <code>JSON_TABLE</code>. 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 <code>JSON</code> 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 <code>json</code>/<code>jsonb</code> 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. [[Category:SQL]] [[Category:JSON]] [[Category:MySQL]] [[Category:PostgreSQL]]