存储对象数组的CLOB列能否按首个对象name属性排序？

我有一个CLOB类型的列，用于存储结构为{"id": number, "name": string}的对象数组。现有如下CLOB记录：

• [{"id": 21, "nombre": "fisica"}, {"id": 16, "nombre": "auditiva"}]
• [{"id": 16, "nombre": "auditiva"}]
• [{"id": 4, "nombre": "intelectual"}, {"id": 21, "nombre": "fisica"}]
  该列每条记录的数组至少包含一个带id和name属性的对象，请问能否按每条记录首个对象的name属性对该列排序？预期结果示例：
• [{"id": 16, "name": "auditiva"}]
• [{"id": 21, "name": "fisica"}, {"id": 16, "name": "auditiva"}]
• [{"id": 4, "name": "intelectual"}, {"id": 21, "name": "fisica"}]

可以实现，以下针对主流数据库给出具体实现方式：

Oracle数据库（CLOB为Oracle常用类型） #

利用Oracle 12c及以上版本支持的JSON处理函数，提取数组首个元素的nombre字段（注：您的存储数据中属性名为nombre，预期结果中写为name，请以实际存储的属性名为准）进行排序：

SELECT your_clob_column
FROM your_table
ORDER BY JSON_VALUE(your_clob_column, '$[0].nombre' RETURNING VARCHAR2(100)) ASC;

• JSON_VALUE：从JSON格式的CLOB数据中提取指定路径的值，$[0].nombre表示取数组第一个元素的nombre属性
• RETURNING VARCHAR2(100)：指定返回值的类型和长度，可根据实际字符串长度调整
• ASC：表示升序排序，若要降序可替换为DESC

其他数据库示例 #

PostgreSQL #

将CLOB转换为JSONB类型后提取属性：

SELECT your_clob_column
FROM your_table
ORDER BY (your_clob_column::jsonb -> 0 ->> 'nombre') ASC;

MySQL #

使用JSON_EXTRACT函数提取目标属性：

SELECT your_clob_column
FROM your_table
ORDER BY JSON_EXTRACT(your_clob_column, '$[0].nombre') ASC;

内容的提问来源于stack exchange，提问作者RockyCott