JSON: Difference between revisions

From MemCP
Jump to navigation Jump to search
(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</...")
 
(Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
= JSON and SQL/JSON in MemCP =
<!-- Copyright (C) 2026 Carl-Philip Haensch -->
<!-- SPDX-License-Identifier: GPL-3.0-or-later -->
= JSON and SQL/JSON =


'''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.
MemCP supports native JSON values through a typed BSON-backed runtime representation. Both the MySQL and PostgreSQL syntax frontends expose constructors, path access, mutation, containment, aggregation, and relational expansion. JSON is useful for attributes whose shape genuinely varies; stable fields used for joins, constraints, or frequent filtering are often clearer as ordinary typed columns.


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.
== Creating and reading JSON ==


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.
MySQL-style constructors and paths include <code>JSON_OBJECT</code>, <code>JSON_ARRAY</code>, <code>JSON_EXTRACT</code>, <code>JSON_VALUE</code>, and the <code>-&gt;</code>/<code>-&gt;&gt;</code> operators.
 
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>
<pre>
SELECT item.ord, item.name
SELECT JSON_OBJECT('name', 'Ada', 'roles', JSON_ARRAY('admin', 'author'));
FROM JSON_TABLE(
SELECT profile->>'$.name' AS name
'[{"name":"Ada"},{"name":"Bob"}]',
FROM users
'$[*]' COLUMNS (
WHERE JSON_EXTRACT(profile, '$.active') = true;
ord FOR ORDINALITY,
name TEXT PATH '$.name'
)
) AS item;
</pre>
</pre>


== MySQL and MariaDB syntax ==
PostgreSQL syntax supports <code>json</code>/<code>jsonb</code> casts and operators such as <code>-&gt;</code>, <code>-&gt;&gt;</code>, <code>#&gt;</code>, <code>#&gt;&gt;</code>, <code>@&gt;</code>, and <code>&lt;@</code>, together with PostgreSQL-style build and path functions.
 
MemCP implements the following MySQL/MariaDB-style JSON surface:


{| class="wikitable"
== Updating and aggregating ==
! 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.
Use <code>JSON_SET</code>, <code>JSON_INSERT</code>, <code>JSON_REPLACE</code>, <code>JSON_REMOVE</code>, array mutation, or merge functions to produce an updated document. JSON values are immutable expressions: an UPDATE assigns the returned value back to the column.


<pre>
<pre>
SELECT JSON_EXTRACT(
UPDATE users
'{"orders":[{"total":10},{"total":25}]}',
SET profile = JSON_SET(profile, '$.last_login', CURRENT_TIMESTAMP)
'$.orders[last].total'
WHERE id = 42;
); -- 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 team_id, JSON_ARRAYAGG(name)
SELECT JSON_OBJECT(
FROM users
'id', delivery_note.id,
GROUP BY team_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>
</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.
Object aggregates, <code>JSON_TABLE</code>, PostgreSQL <code>json_array_elements</code>, and object-key expansion turn documents into relational rows or collect rows into documents. Their exact accepted syntax differs between the MySQL and PostgreSQL endpoints; test queries against the selected frontend.
 
== 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.
== Indexing and compatibility ==


=== Should all application data be stored in one JSON column? ===
Frequently used path expressions can participate in computed-expression optimization and adaptive indexing. Keep the path expression stable and inspect the physical plan with <code>EXPLAIN PHYSICAL</code>; an accepted JSON predicate does not by itself guarantee an index.


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.
JSON numbers, NULL, SQL NULL, missing paths, duplicate object keys, and scalar-versus-container results have compatibility-sensitive semantics. Validate them when migrating from MySQL or PostgreSQL. The executable regression suites <code>tests/sql/expressions/json-functions.yaml</code> and <code>postgresql-json-functions.yaml</code> are the most precise inventory for the current commit.


[[Category:SQL]]
See [[Supported SQL]], [[Migration from MySQL and PostgreSQL]], [[SQL over REST]], and [[Data Auto Sharding and Auto Indexing]].
[[Category:JSON]]
[[Category:MySQL]]
[[Category:PostgreSQL]]

Latest revision as of 12:13, 28 August 2026

JSON and SQL/JSON

MemCP supports native JSON values through a typed BSON-backed runtime representation. Both the MySQL and PostgreSQL syntax frontends expose constructors, path access, mutation, containment, aggregation, and relational expansion. JSON is useful for attributes whose shape genuinely varies; stable fields used for joins, constraints, or frequent filtering are often clearer as ordinary typed columns.

Creating and reading JSON

MySQL-style constructors and paths include JSON_OBJECT, JSON_ARRAY, JSON_EXTRACT, JSON_VALUE, and the ->/->> operators. 

SELECT JSON_OBJECT('name', 'Ada', 'roles', JSON_ARRAY('admin', 'author'));
SELECT profile->>'$.name' AS name
FROM users
WHERE JSON_EXTRACT(profile, '$.active') = true;

PostgreSQL syntax supports json/jsonb casts and operators such as ->, ->>, #>, #>>, @>, and <@, together with PostgreSQL-style build and path functions.

Updating and aggregating

Use JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, array mutation, or merge functions to produce an updated document. JSON values are immutable expressions: an UPDATE assigns the returned value back to the column. 

UPDATE users
SET profile = JSON_SET(profile, '$.last_login', CURRENT_TIMESTAMP)
WHERE id = 42;

SELECT team_id, JSON_ARRAYAGG(name)
FROM users
GROUP BY team_id;

Object aggregates, JSON_TABLE, PostgreSQL json_array_elements, and object-key expansion turn documents into relational rows or collect rows into documents. Their exact accepted syntax differs between the MySQL and PostgreSQL endpoints; test queries against the selected frontend.

Indexing and compatibility

Frequently used path expressions can participate in computed-expression optimization and adaptive indexing. Keep the path expression stable and inspect the physical plan with EXPLAIN PHYSICAL; an accepted JSON predicate does not by itself guarantee an index.

JSON numbers, NULL, SQL NULL, missing paths, duplicate object keys, and scalar-versus-container results have compatibility-sensitive semantics. Validate them when migrating from MySQL or PostgreSQL. The executable regression suites tests/sql/expressions/json-functions.yaml and postgresql-json-functions.yaml are the most precise inventory for the current commit.

See Supported SQL, Migration from MySQL and PostgreSQL, SQL over REST, and Data Auto Sharding and Auto Indexing.

Retrieved from "https://www.memcp.org/index.php?title=JSON&oldid=372"