SQLite中json_object(*)无效原因及批量生成JSON对象替代方案咨询

1. 为什么json_object(*)仅返回{}？ #

SQLite的json_object()函数必须成对传入键名和对应的值，比如json_object('col1', col1, 'col2', col2)这种明确的键值对格式。它从设计上就不支持*作为通配符来自动遍历表的所有列。当你写json_object(*)时，这里的*会被当作一个无对应值的字符串键，不符合函数的参数要求，最终只能生成空的JSON对象{}。

2. 更简便的替代实现方式 #

方式一：动态拼接SQL语句（最实用） #

利用SQLite的pragma table_info()获取目标表的所有列名，自动拼接出json_object()需要的键值对参数：

1. 先执行以下语句获取列名拼接字符串：

   SELECT group_concat("'" || name || "', " || name, ', ') AS json_object_params
   FROM pragma table_info('table1');
   

   这条语句会返回类似'col1', col1, 'col2', col2的结果。
2. 把这个结果拼到主查询中，最终执行：

   SELECT json_group_array(json_object(/* 上面得到的拼接字符串 */)) AS data FROM table1;
   

   如果是在应用程序中调用，可以先执行第一步的查询拿到参数字符串，再动态生成并执行主SQL，完全不用手动写所有列。

方式二：利用系统表与聚合函数组合（适合临时查询） #

如果不想动态拼接，可以通过关联表结构信息和数据行来生成JSON对象：

SELECT json_group_array(
  (SELECT json_group_object(name, value)
   FROM pragma table_info('table1') AS cols
   JOIN (SELECT * FROM table1 WHERE table1.rowid = t.rowid) AS rows
   ON cols.name = rows.name
  )
) AS data
FROM table1 AS t;

不过这种方式性能不如动态拼接，适合小表临时使用。

方式三：自定义SQL函数（适合长期复用） #

如果你的使用场景允许扩展SQLite，可以自定义一个row_to_json()函数，让它自动将当前行转换为JSON对象。之后就能直接用：

SELECT json_group_array(row_to_json(*)) AS data FROM table1;

具体实现需要借助SQLite的C扩展接口，或者使用支持自定义函数的驱动（比如Python的sqlite3模块可以注册自定义函数）。

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