JSON-like SQL：强大的JSON数据查询工具

在现代Web开发中，处理JSON数据是一项常见任务。虽然数据库提供了强大的查询功能，但有时我们需要在JavaScript中直接操作JSON数据时也能有类似的查询功能。这就是JSON-like SQL插件的作用——它为JSON数据结构带来了类似SQL的查询能力。

JSON-like SQL简介

JSON-like SQL是一个轻量级的JavaScript库，使开发人员能够在JSON数据上执行类似SQL的操作。它提供了熟悉的SQL结构，如SELECT、WHERE、GROUP BY、JOIN操作，以及COUNT、SUM、AVG、MIN、MAX和CONCAT等聚合函数。

安装

npm install json-like-sql
# 或
pnpm add json-like-sql
# 或
yarn add json-like-sql

入门指南

基本初始化

要开始使用JSON-like SQL插件，首先需要用你的JSON数据初始化它：

import Json from "json-like-sql";

const data = [
  {
    taskIdTX: 1,
    dateTime: '13',
    taskLocationId: '11',
    score: 85
  },
  {
    taskIdTX: 2,
    dateTime: '12',
    taskLocationId: '1',
    score: 90
  },
  // ... 更多数据
];

const db = new Json(data);

使用JSON字符串初始化

// 字符串必须是数组格式的JSON
const jsonStr = '[{"id":1,"name":"A"},{"id":2,"name":"B"}]';
const db = new Json(jsonStr);

使用setContent方法更新数据

// 使用数组更新数据
const newData = [
  { id: 3, name: 'C' },
  { id: 4, name: 'D' }
];
db.setContent(newData);

// 使用JSON字符串更新数据（必须是数组格式）
const jsonStr = '[{"id":5,"name":"E"},{"id":6,"name":"F"}]';
db.setContent(jsonStr);

// 注意：不支持单个对象
db.setContent({ id: 1, name: 'test' }); // 控制台警告：请传入字符串或数组

使用SELECT进行基本查询

SELECT操作允许你从JSON数据中提取特定字段。你还可以使用别名来重命名字段：

// 选择特定字段并使用别名
db.select(['taskLocationId', 'dateTime as dt']).get();

/* 结果:
[
  {"taskLocationId":"11","dt":"13"},
  {"taskLocationId":"1","dt":"12"},
  {"taskLocationId":"1","dt":"166"},
  {"taskLocationId":"2","dt":"17"},
  {"taskLocationId":"1","dt":"16"}
]
*/

选择所有字段

使用通配符 * 选择所有字段：

db.select(['*']).get();
// 返回原始数据的所有字段

多次select合并字段

多次调用select会合并字段，后面的字段可以覆盖前面同名的字段：

db.select(['taskIdTX'])
  .select(['score'])
  .get();

/* 结果:
[
  {"taskIdTX":1,"score":85},
  {"taskIdTX":2,"score":90},
  ...
]
*/

使用GROUP BY对数据进行分组

就像在SQL中一样，你可以使用GROUP BY子句按一个或多个字段对记录进行分组：

db.select(['taskLocationId', 'dateTime as dt'])
  .groupBy(['taskLocationId'])
  .get();

/* 结果:
[
  {
    "taskLocationId":"11",
    "children":[
      {"taskLocationId":"11","dt":"13"}
    ]
  },
  {  
    "taskLocationId":"1",
    "children":[
      {"taskLocationId":"1","dt":"12"},
      {"taskLocationId":"1","dt":"166"},
      {"taskLocationId":"1","dt":"16"}
    ]
  },
  // ... 更多分组
]
*/

聚合函数

JSON-like SQL最强大的功能之一是支持聚合函数。当与GROUP BY结合使用时，你可以计算分组数据的统计信息：

db.groupBy(['taskLocationId'])
  .select([
    'taskLocationId as id', 
    'count(dateTime) as count',
    'sum(dateTime) as sum',
    'min(dateTime) as min',
    'max(dateTime) as max',
    'avg(dateTime) as avg',
    'concat(dateTime) as dtstr',
  ])
  .get();

/* 结果:
[
  {
    "id":"11",
    "count":1,
    "sum":13,
    "min":13,
    "max":13,
    "avg":13,
    "dtstr":"13",
    "children":[...]
  },
  // ... 更多聚合分组
]
*/

支持的聚合函数包括：

• count() - 计算项目数量
• sum() - 计算值的总和
• min() - 查找最小值
• max() - 查找最大值
• avg() - 计算平均值
• concat() - 连接字符串值

连接操作

JSON-like SQL支持LEFT JOIN和INNER JOIN操作，允许你合并来自多个JSON数组的数据：

左连接

LEFT JOIN返回左数据集中的所有记录和右数据集中的匹配记录：

const tt = [
  {
    dateTime: '12',
    age: '123',
    name: 'Alice',
    xx: '11',
    yy: '13',
  },
  {
    dateTime: '13',
    age: '33',
    name: 'Bob',
    xx: '55',
    yy: '66',
  }
];

db.select(['dateTime'])
  .leftJoin(tt, 'dateTime')
  .get();

/* 结果:
[
  {"dateTime":"13","age":"33","name":"Bob","xx":"55","yy":"66"},
  {"dateTime":"12","age":"123","name":"Alice","xx":"11","yy":"13"},
  {"dateTime":"166"},  // 无匹配数据，只返回左表数据
  {"dateTime":"17"},
  {"dateTime":"16"}
]
*/

选择连接表的特定字段

db.select(['dateTime'])
  .leftJoin(tt, 'dateTime', ['name as userName', 'age'])
  .sort('dateTime', 'asc')
  .get();

/* 结果:
[
  {"dateTime":"12","userName":"Alice","age":"123"},
  {"dateTime":"13","userName":"Bob","age":"33"},
  {"dateTime":"166"},
  {"dateTime":"17"},
  {"dateTime":"16"}
]
*/

内连接

INNER JOIN只返回在两个数据集中都有匹配值的记录：

db.select(['dateTime'])
  .innerJoin(tt, 'dateTime')
  .get();

/* 结果:
[
  {"dateTime":"13","age":"33","name":"Bob","xx":"55","yy":"66"},
  {"dateTime":"12","age":"123","name":"Alice","xx":"11","yy":"13"}
]
*/

多表连接

可以链式调用多个join操作：

const table2 = [...];
const table3 = [...];

db.select(['id'])
  .innerJoin(table2, 'id')
  .leftJoin(table3, 'id', ['name'])
  .get();

高级功能

使用WHERE进行过滤

你可以使用WHERE方法和各种操作符来过滤结果：

db.where('taskLocationId', '=', '1')
  .select(['taskIdTX', 'dateTime'])
  .get();

支持的比较操作符

| 操作符 | 说明 | 示例 | |--------|------|------| | = 或 eq | 等于 | where('score', '=', 85) | | != 或 neq | 不等于 | where('score', '!=', 85) | | > 或 gt | 大于 | where('score', '>', 80) | | < 或 lt | 小于 | where('score', '<', 90) | | >= 或 gte | 大于等于 | where('score', '>=', 80) | | <= 或 lte | 小于等于 | where('score', '<=', 90) | | in | 包含于数组中 | where('taskLocationId', 'in', ['1', '2']) | | notIn | 不包含于数组中 | where('taskLocationId', 'notIn', ['11']) | | startsWith | 字符串以此开头 | where('dateTime', 'startsWith', '1') | | endsWith | 字符串以此结尾 | where('dateTime', 'endsWith', '6') | | contains | 字符串包含 | where('dateTime', 'contains', '1') |

多个WHERE条件

可以链式调用多个where条件，所有条件必须同时满足（AND逻辑）：

db.where('taskLocationId', '=', '1')
  .where('score', '>', 75)
  .select(['taskIdTX', 'taskLocationId', 'score'])
  .sort('score', 'desc')
  .get();

数据排序

按升序或降序对结果进行排序：

db.select(['taskLocationId', 'dateTime'])
  .sort('dateTime', 'asc')
  .get();

// 降序排序
db.select(['taskLocationId', 'dateTime'])
  .sort('dateTime', 'desc')
  .get();

注意：多次调用sort，后面的会覆盖前面的排序规则。

快速获取首尾数据

使用 first() 和 last() 方法快速获取第一条和最后一条数据：

const firstItem = db.first();
const lastItem = db.last();

// 如果数据为空，返回 null

限制结果

使用LIMIT和OFFSET控制返回记录的数量：

db.select(['taskLocationId', 'dateTime'])
  .limit(5)
  .offset(2)
  .get();

• limit(n)：限制返回n条记录（当 n <= 0 时返回全部数据）
• offset(n)：跳过前n条记录，从第n+1条开始返回

示例：分页查询

// 每页10条，获取第2页（跳过前10条）
db.select(['id', 'name'])
  .limit(10)
  .offset(10)
  .get();

实际应用

JSON-like SQL在以下场景中特别有用：

1. 数据分析：在浏览器中处理和分析大型JSON数据集
2. 报告生成：使用分组和聚合创建汇总报告
3. 数据转换：转换数据结构以匹配API要求
4. 客户端过滤：在不进行后端请求的情况下过滤和排序数据
5. 原型设计：在实施服务器端解决方案之前快速构建数据操作逻辑

完整示例

场景1：电商数据分析

const orders = [
  { id: 1, userId: 101, product: 'A', amount: 100, date: '2024-01-01' },
  { id: 2, userId: 102, product: 'B', amount: 200, date: '2024-01-02' },
  { id: 3, userId: 101, product: 'C', amount: 150, date: '2024-01-03' },
  { id: 4, userId: 103, product: 'A', amount: 100, date: '2024-01-04' },
];

// 统计每个用户的订单数量和总金额
const result = new Json(orders)
  .select([
    'userId',
    'count(id) as orderCount',
    'sum(amount) as totalAmount',
    'avg(amount) as avgAmount'
  ])
  .groupBy(['userId'])
  .sort('totalAmount', 'desc')
  .get();

场景2：学生成绩管理

const students = [
  { id: 1, name: '张三', classId: '1', score: 85 },
  { id: 2, name: '李四', classId: '1', score: 90 },
  { id: 3, name: '王五', classId: '2', score: 75 },
  { id: 4, name: '赵六', classId: '2', score: 80 },
];

const classes = [
  { id: '1', name: '一班', teacher: '张老师' },
  { id: '2', name: '二班', teacher: '李老师' },
];

// 查询成绩大于80分的学生及其班级信息
const result = new Json(students)
  .where('score', '>', 80)
  .select(['name', 'score', 'classId'])
  .innerJoin(classes, 'classId', ['name as className', 'teacher'])
  .sort('score', 'desc')
  .get();

/* 结果:
[
  {"name":"李四","score":90,"classId":"1","className":"一班","teacher":"张老师"},
  {"name":"张三","score":85,"classId":"1","className":"一班","teacher":"张老师"}
]
*/

场景3：日志数据分析

const logs = [
  { id: 1, level: 'INFO', message: 'User login', timestamp: '1704067200' },
  { id: 2, level: 'ERROR', message: 'Database error', timestamp: '1704067260' },
  { id: 3, level: 'WARN', message: 'High memory', timestamp: '1704067320' },
  { id: 4, level: 'ERROR', message: 'Connection timeout', timestamp: '1704067380' },
];

// 按日志级别分组，统计数量
const result = new Json(logs)
  .select([
    'level',
    'count(id) as count',
    'concat(message) as messages'
  ])
  .groupBy(['level'])
  .sort('count', 'desc')
  .get();

/* 结果:
[
  {"level":"INFO","count":1,"messages":"User login"},
  {"level":"ERROR","count":2,"messages":"Database error,Connection timeout"},
  {"level":"WARN","count":1,"messages":"High memory"}
]
*/

注意事项

1. 字段嵌套：目前不支持嵌套字段（如 info.name），如需使用嵌套字段会打印警告信息
2. 排序稳定性：字符串数字会按数值大小排序（如 '12' < '166' < '17'）
3. 链式调用：多次调用 select 会合并字段，多次调用 sort 会覆盖之前的排序规则
4. 空数据处理：空数组查询返回空数组，first() 和 last() 返回 null

性能建议

1. 先过滤再排序：先使用 where 减少数据量，再进行排序和分组操作
2. 限制结果数量：使用 limit 限制返回的数据量，提高性能
3. 避免过度分组：groupBy 会产生较大的中间数据，谨慎使用

结论

JSON-like SQL在传统SQL数据库和客户端JavaScript应用程序之间架起了一座桥梁。凭借其类似SQL语法的直观API，开发人员可以快速学习并在他们的应用程序中实现强大的数据查询功能。无论你是处理API响应、分析用户数据，还是转换复杂的JSON结构，JSON-like SQL都提供了高效便捷地处理JSON数据所需的工具。

它将熟悉的SQL语义与JavaScript的灵活性相结合，使其成为任何需要广泛处理JSON数据操作的开发人员工具包中的宝贵补充。