自从jOOQ 3.14在2020年10月19日发布，支持SQL/JSON（和SQL/XML）以来，已经过去了将近1年。半年后，我们又发布了支持MULTISET 的jOOQ 3.15，它建立在这些功能之上，提供类型安全的嵌套集合，这是每个ORDBMS应该实现的方式。
在我们自己的SQL/JSON API的基础上进行构建（dogfooding），揭示了不同供应商的各种SQL/JSON实现的很多注意事项，坦率地说，这是一个有点清醒的经验。尽管现在有了ISO/IEC TR 19075:6标准（这次主要是由Oracle推动的），许多供应商已经实现了某种JSON支持，而且它在所有的方言中看起来都不一样--以至于用手写的本地SQL编写供应商无关的SQL/JSON几乎不可能。你需要一个像jOOQ这样的API或者其他的抽象概念来规范不同的方言。
在这篇文章中，我想分享一下过去一年中遇到的一些最大的注意事项。还有很多，只要尝试用jOOQ将一些标准SQL/JSON翻译成各种方言就可以了。
JSON类型还是字符串？
JSON文档可以被看作是具有格式化内容的简单字符串。当然，我的意思是你可以把字符串解析成JSON文档，那么为什么要用新的类型把SQL类型系统复杂化呢？
不幸的是，这被证明是大多数供应商的一个错误决定。PostgreSQL通过提供JSON 和JSONB 类型来解决这个问题，后者即使在存储和索引JSON文档的时候也是相当出色的。
首先，对于任何使用SQL以外的其他语言的人来说，这并不奇怪。类型是有语义的。串联类型的环境是不好的。或者就像那句话说的。
质量差的苦味在低价的甜味被遗忘后还会长期存在--本杰明-富兰克林
"低价 "是指没有正式类型的快速和肮脏的串联类型的功能添加的低价。我们已经在BOOLEAN 类型上一次又一次地出现了这种情况，还有像MySQL这样的方言，假装支持这种情况是一个好主意：
SELECT *
FROM t
WHERE 1 OR 0 -- TRUE OR FALSE
让我们来看看我的意思的例子。
MariaDB和MySQL
让我们先看一下MariaDB和MySQL的语法。下面是如何创建一个JSON数组：
select json_array(1, 2)
生成
[1, 2]
这很好!而且它甚至符合标准的SQL语法。一个JSON数组也可以很容易地进行嵌套：
select json_array(json_array(1, 2), 3)
来产生：
[[1, 2], 3]
现在，如果这个嵌套数组来自于一个派生表呢？
select json_array(nested, 3)
from (select json_array(1, 2) nested) as t
它的结果是：
-- MySQL, MariaDB 10.6
[[1, 2], 3]
-- MariaDB 10.5
["[1, 2]", 3]
遗憾的是。在MariaDB 10.5中，嵌套数组失去了它的 "JSON类型注释"，回到了它的字符串类型版本。作为一个字符串，它需要被引号。这似乎是一个错误jira.mariadb.org/browse/MDEV…，显然已经在MariaDB 10.6的一个不同的问题下得到了修复。但这并不是唯一的问题。Bug跟踪器上有很多类似的问题：https://jira.mariadb.org/browse/MDEV-13701。
MySQL现在似乎好一点了，尽管在聚合时有一些注意事项（见下文）。
到目前为止，我为上述错误找到的唯一解决方法是非常 费力的：
select json_array(json_merge_preserve('[]', nested), 3)
from (select json_array(1, 2) nested) as t
想象一下，当你每次嵌套JSON时都要这样做。它是可行的，但它真的需要自动化（例如通过jOOQ）。
甲骨文
Oracle定义了大部分的SQL标准，我非常喜欢它的SQL-idiomatic语法感觉，就像SQL/XML。不幸的是，他们把新的JSON类型的引入等到了Oracle 21c（迫不及待地想使用它）。因此，我们必须选择是将JSON表示为VARCHAR2 ，默认情况下（在某些系统上限制为4000字节，或最多32kb！）还是CLOB 。如果你在做严肃的JSON，你可能总是可以在你的每一个 JSON函数调用中添加RETURNING CLOB 子句。有效地：
select json_array(1, 2 returning clob)
from dual
嵌套的版本看起来像这样：
select json_array(nested, 3 returning clob)
from (select json_array(1, 2 returning clob) nested from dual) t;
这在Oracle中的效果要好得多，但仍有许多边缘情况没有得到解决。在Oracle 18c上试试这个：
select json_arrayagg(coalesce(json_object(), json_object()))
from dual
它产生了另一个错误的字符串类型的JSON实例：
["{}"]
解决办法是在所有地方添加FORMAT JSON ，只是为了确定，例如：
select json_arrayagg(
coalesce(json_object(), json_object()) format json
)
from dual
现在，结果与预期一致：
[{}]
为了安全起见，你可能又要在所有地方 写上FORMAT JSON ，就像RETURNING CLOB
截断
截断可能是使用JSON时最令人担忧的部分。为什么我们在SQL中仍然有大小限制的数据类型？从应用的角度来看，这几乎没有任何意义。然而，我们在这里。
甲骨文
在Oracle中运行这个查询：
select json_arrayagg(owner) from all_objects;
你会得到：
SQL错误 [40478] [99999]。ORA-40478: 输出值过大（最大：4000）。
将最大的VARCHAR2 大小增加到32kb，只会推迟问题的出现。这类文件没有 "合理 "的大小限制，所以同样，你必须一直 RETURNING CLOB 。
select json_arrayagg(owner returning clob) from all_objects;
代价是一如既往的。CLOB ，只是比VARCHAR2 ，无论是在Oracle内部还是在客户端应用程序（例如基于JDBC的）中，都更令人讨厌，因为当你只是想要一个字符串的时候，你必须与另一个资源打交道。jOOQ将为你到处添加子句，几乎没有理由不使用jOOQ，因为获取CLOB 的值对jOOQ用户是完全透明的。
MySQL
直到最近，你还不能在MySQL中调用JSON_ARRAYAGG() ，而且MariaDB版本会使服务器崩溃（https://jira.mariadb.org/browse/MDEV-21912）。当写这篇博客的时候，这两个实现都不支持JSON_ARRAYAGG() 中的ORDER BY 子句，我认为这是相当重要的，所以变通的办法是使用GROUP_CONCAT：
select concat('[', group_concat(id), ']')
from t_book
当然，如果串联的值不是数字，那就大错特错了，所以我们还需要使用JSON_QUOTE ，比如说：
select concat('[', group_concat(json_quote(title)), ']')
from t_book
而且，如果你在其他JSON结构中嵌入这些东西，你必须使用JSON_MERGE （直到最近，但现在已经废弃）或JSON_MERGE_PRESERVE ，把现在真正的字符串变成JSON，例如：
select json_object(
'titles',
json_merge_preserve(
'[]',
concat('[', group_concat(json_quote(title)), ']')
)
)
from t_book
来产生这样的文件：
{"titles": ["1984", "Animal Farm", "O Alquimista", "Brida"]}
如果没有这个JSON_MERGE_PRESERVE ，你就会得到：
{"titles": "[\"1984\",\"Animal Farm\",\"O Alquimista\",\"Brida\"]"}
绝对不是你能记住的东西。
无论如何。这一节是关于截断的!在MySQL中，对于大型的、聚合的JSON文档会发生什么？试试这个：
select
concat('[', group_concat(json_quote(table_name)), ']')
from information_schema.tables
它产生（在我的机器上）：
["innodb_table_stats","innodb_index_stats","CHARACTER_SETS","CHECK_CONSTRAINTS","COLLATIONS","COLLATION_CHARACTER_SET_APPLICABILITY","COLUMNS","COLUMNS_EXTENSIONS","COLUMN_STATISTICS","EVENTS","FILES","INNODB_DATAFILES","INNODB_FOREIGN","INNODB_FOREIGN_COLS","INNODB_FIELDS","INNODB_TABLESPACES_BRIEF","KEY_COLUMN_USAGE","KEYWORDS","PARAMETERS","PARTITIONS","REFERENTIAL_CONSTRAINTS","RESOURCE_GROUPS","ROUTINES","SCHEMATA","SCHEMATA_EXTENSIONS","ST_SPATIAL_REFERENCE_SYSTEMS","ST_UNITS_OF_MEASURE","ST_GEOMETRY_COLUMNS","STATISTICS","TABLE_CONSTRAINTS","TABLE_CONSTRAINTS_EXTENSIONS","TABLES","TABLES_EXTENSIONS","TABLESPACES_EXTENSIONS","TRIGGERS","VIEW_ROUTINE_USAGE","VIEW_TABLE_USAGE","VIEWS","COLUMN_PRIVILEGES","ENGINES","OPTIMIZER_TRACE","PLUGINS","PROCESSLIST","PROFILING","SCHEMA_PRIVILEGES","TABLESPACES","TABLE_PRIVILEGES","USER_PRIVILEGES","cond_instances","error_log","events_waits_current","events_waits_history","events_waits_history_long","events_waits_summary_by_host_by_event_name","events_waits_summary_by]
等等，结尾处是什么？
,"events_waits_summary_by]
无效的JSON。因为GROUP_CONCAT 字符串被截断了。我们可以设置如下：
set @@group_concat_max_len = 4294967295;
而现在输出是正确的，而且更长：
["innodb_table_stats",...,"t_identity_pk","t_triggers"]
像jOOQ这样的API将能够为你自动设置这个会话变量，但你可能不想为本地SQL一直考虑这个问题？
数据类型支持
JSON知道一些数据类型。即。
- 字符串
- 数字
- 布尔型
- 空
- 对象
- 数组
这比SQL要少，但往往足够好（毕竟任何东西都可以编码为字符串）。但是当你在SQL中没有BOOLEAN 类型时（例如MySQL，Oracle），那么你就必须手动将你已经手动编码为JSONBOOLEAN 。
MySQL
MySQL让你相信这是可行的：
select json_array(true, false)
因为上面产生
[true, false]
但它并没有真正发挥作用。这似乎是解析器中的硬编码。只要你的true 和false 值是表达式，而不是字面意义，例如，源自一个派生表：
select json_array(t, f)
from (select true as t, false as f) as t
你就会得到：
[1, 0]
有不同的方法来模拟这一点。一种是：
select json_array(
json_extract(case when t = 1 then 'true' when t = 0 then 'false' end, '$'),
json_extract(case when f = 1 then 'true' when f = 0 then 'false' end, '$')
)
from (select true as t, false as f) as t：
现在，我们又得到了：
[true, false]
Oracle
不像MySQL / MariaDB，Oracle SQL不假装它有一个BOOLEAN 类型。相反，人们把它编码为NUMBER(1) 或CHAR(1) 或其他一些东西。不管是哪种编码，这就是解决方案。
select json_array(
case when t = 1 then 'true' when t = 0 then 'false' end format json,
case when f = 1 then 'true' when f = 0 then 'false' end format json
)
from (
select 1 as t, 0 as f, null as n
from dual
) t
生成
[true,false]
NULL处理
当用SQL/JSON使用NULL ，有各种注意事项。首先，SQLNULL 与JSONNULL 不是一回事。使用PostgreSQL。
select
a,
b,
a = b as equal,
a is null as a_is_null,
b is null as b_is_null
from (
select null::jsonb as a, 'null'::jsonb as b
) as t
产生了：
|a |b |equal|a_is_null|b_is_null|
|---|----|-----|---------|---------|
| |null| |true |false |
其中空的单元格是SQLNULL 值，而null 值是JSONnull 值，不是 "SQLNULL"。这是唯一合理的做法，真的。各方言是否同意？
MySQL
select
a,
b,
a = b as equal,
a is null as a_is_null,
b is null as b_is_null
from (
select null as a, json_extract('null', '$') as b
) as t
产生的也是：
|a |b |equal|a_is_null|b_is_null|
|---|----|-----|---------|---------|
| |null| |1 |0 |
所以，是的!
Oracle
让我们看看，这可能是我如何创建一个JSONNULL 值：
select
a,
b,
case when a = b then 1 else 0 end as equal,
case when a is null then 1 else 0 end as a_is_null,
case when b is null then 1 else 0 end as b_is_null
from (
select null as a, json_value('[null]', '$[0]') as b
from dual
) t
然而，众所周知，Oracle与NULL 字符串有一种古怪的关系。这就是结果：
|A |B |EQUAL|A_IS_NULL|B_IS_NULL|
|---|---|-----|---------|---------|
| | |0 |1 |1 |
似乎没有一个实际的JSONNULL 表示！我还没有找到解决这个问题的办法。也许我会的。但这是非常不幸的，导致了许多转换的边缘情况。
另一方面，在本文介绍的方言中，Oracle是唯一为聚合函数引入了非常有用的NULL 处理条款的方言。请看这个：
select
json_arrayagg(a),
json_arrayagg(a absent on null),
json_arrayagg(a null on null)
from (
select 1 as a from dual union all
select null from dual
) t
生成
|A |B |C |
|---|---|--------|
|[1]|[1]|[2,null]|
注意上述查询在Oracle 18c上还不能产生正确的结果，因为有一个解析器/优化器的错误。在这个例子中，使用这个方法来解决这个错误：
select
json_arrayagg(a) a,
json_arrayagg(a + 0 absent on null) b,
json_arrayagg(a - 0 null on null) c
from (
select 1 as a from dual union all
select null from dual
) t
其他方言对如何将NULL 值聚合到JSON文档有不同的意见。在SQL中，聚合函数倾向于忽略NULL 值，比如上面的Oracle，但是对于JSON，通常必须包括该值，特别是在创建JSON_OBJECT ，其中没有键和没有值严格来说是两回事。
Db2
Db2对SQL/JSON的实现非常有限。它在语法上非常符合标准，但却存在着严重的错误，比如这些。
这使得它目前还无法使用。当然，这将在不久的将来得到改善。
SQL服务器
在这篇文章中，我还漏掉了SQL Server。SQL Server有一段时间支持JSON和XML，但实现方式完全不同。你不能轻易地形成任意的JSON对象或数组，但你可以以一种直观的方式将结果集转化为JSON。
这在流式处理结果时提供了一个快速的胜利，但并不能很好地进行合成。例如，你不能用标量、非对象的内容创建一个JSON_ARRAY ，尽管可以这样创建一个JSON_OBJECT ：
select 1 as a, 2 as b
for json path, without_array_wrapper
制作
{"a":1,"b":2}
在SQL Server中也可以模拟有限数量的JSON特性，和所有不支持实际JSON类型的方言一样，可能需要不断地转义/取消转义。
总结
SQL/JSON的标准化相对较晚，主要是由Oracle制定的。这个标准是非常完善的。但遗憾的是，很多方言在语法和行为上存在分歧，有些方言还存在很大的问题。
在所有的实现中，最健全的是PostgreSQL，它引入了适当的数据类型，并提供了一套丰富的供应商特定的函数来直接在SQL中操作JSON。在不久的将来，PostgreSQL会像Oracle一样接受标准的SQL/JSON，并加强自己的实现。我不认为会有什么根本性的新东西，只是会有更多可移植的标准语法。
关于 "下一步是什么 "的预览，请看这个演讲。
对于像jOOQ这样的库来说，没有什么是不可逾越的，它抽象了所有的方言，并使用统一的、受标准启发的API提供SQL/JSON（和SQL/XML）功能。随着方言的改进，jOOQ将采用各个供应商的新的、更好的语法，所以你可以实现你的SQL/JSON查询的兼容性。
因此，像往常一样，使用jOOQ来解决各厂商语法的微妙而无聊的差异，并在今天就已经开始使用SQL/JSON。尽管这里提出了一些注意事项，但SQL/JSON是现代SQL中最令人兴奋的事情之一
阅读更多：
