@minatojs/sql-utils - npm Package Compare versions

Comparing version 4.0.7 to 4.1.0

lib/index.d.ts

@@ -18,2 +18,8 @@ import { Dict } from 'cosmokit';
 }
+type SQLType = 'raw' | 'json' | 'list';
+interface State {
+    sqlType?: SQLType;
+    sqlTypes?: Dict<SQLType>;
+    group?: boolean;
+}
 export declare class Builder {
@@ -27,5 +33,7 @@ tables?: Dict<Model<any>> | undefined;
     protected evalOperators: EvalOperators;
+    protected state: State;
     constructor(tables?: Dict<Model<any>> | undefined);
+    protected unescapeId(value: string): string;
     protected createNullQuery(key: string, value: boolean): string;
-    protected createMemberQuery(key: string, value: any[], notStr?: string): string;
+    protected createMemberQuery(key: string, value: any, notStr?: string): string;
     protected createRegExpQuery(key: string, value: string | RegExp): string;
@@ -38,13 +46,21 @@ protected createElementQuery(key: string, value: any): string;
     protected logicalNot(condition: string): string;
+    protected jsonLength(value: string): string;
+    protected jsonContains(obj: string, value: string): string;
+    protected jsonUnquote(value: string, pure?: boolean): string;
+    protected jsonQuote(value: string, pure?: boolean): string;
+    protected createAggr(expr: any, aggr: (value: string) => string, nonaggr?: (value: string) => string): string;
+    protected groupObject(fields: any): string;
+    protected groupArray(value: string): string;
     protected parseFieldQuery(key: string, query: Query.FieldExpr): string;
     parseQuery(query: Query.Expr): string;
     private parseEvalExpr;
-    private parseAggr;
+    protected transformJsonField(obj: string, path: string): string;
     private transformKey;
     private getRecursive;
-    parseEval(expr: any): string;
+    parseEval(expr: any, unquote?: boolean): string;
     suffix(modifier: Modifier): string;
-    get(sel: Selection.Immutable, inline?: boolean): any;
+    get(sel: Selection.Immutable, inline?: boolean, group?: boolean): any;
     define<S, T>(converter: Transformer<S, T>): void;
     dump(model: Model, obj: any): any;
+    load(obj: any): any;
     load(model: Model, obj: any): any;
@@ -55,1 +71,2 @@ escape(value: any, field?: Field): string;
 }
+export {};

lib/index.js

@@ -73,5 +73,10 @@ "use strict";
       $size: (key, value) => {
+        var _a;
         if (!value)
           return this.logicalNot(key);
-        return `${key} AND LENGTH(${key}) - LENGTH(REPLACE(${key}, ${this.escape(",")}, ${this.escape("")})) = ${this.escape(value)} - 1`;
+        if (((_a = this.state.sqlTypes) == null ? void 0 : _a[this.unescapeId(key)]) === "json") {
+          return `${this.jsonLength(key)} = ${this.escape(value)}`;
+        } else {
+          return `${key} AND LENGTH(${key}) - LENGTH(REPLACE(${key}, ${this.escape(",")}, ${this.escape("")})) = ${this.escape(value)} - 1`;
+        }
       }
@@ -102,8 +107,22 @@ };
       $lte: this.binary("<="),
+      // membership
+      $in: ([key, value]) => this.createMemberQuery(this.parseEval(key), value, ""),
+      $nin: ([key, value]) => this.createMemberQuery(this.parseEval(key), value, " NOT"),
       // aggregation
-      $sum: (expr) => `ifnull(sum(${this.parseAggr(expr)}), 0)`,
-      $avg: (expr) => `avg(${this.parseAggr(expr)})`,
-      $min: (expr) => `min(${this.parseAggr(expr)})`,
-      $max: (expr) => `max(${this.parseAggr(expr)})`,
-      $count: (expr) => `count(distinct ${this.parseAggr(expr)})`
+      $sum: (expr) => this.createAggr(expr, (value) => `ifnull(sum(${value}), 0)`),
+      $avg: (expr) => this.createAggr(expr, (value) => `avg(${value})`),
+      $min: (expr) => this.createAggr(expr, (value) => `(0+min(${value}))`),
+      $max: (expr) => this.createAggr(expr, (value) => `(0+max(${value}))`),
+      $count: (expr) => this.createAggr(expr, (value) => `count(distinct ${value})`),
+      $length: (expr) => this.createAggr(expr, (value) => `count(${value})`, (value) => {
+        if (this.state.sqlType === "json") {
+          this.state.sqlType = "raw";
+          return `${this.jsonLength(value)}`;
+        } else {
+          this.state.sqlType = "raw";
+          return `if(${value}, LENGTH(${value}) - LENGTH(REPLACE(${value}, ${this.escape(",")}, ${this.escape("")})) + 1, 0)`;
+        }
+      }),
+      $object: (fields) => this.groupObject(fields),
+      $array: (expr) => this.groupArray(this.parseEval(expr, false))
     };
@@ -117,2 +136,6 @@ }
   evalOperators;
+  state = {};
+  unescapeId(value) {
+    return value.slice(1, value.length - 1);
+  }
   createNullQuery(key, value) {
@@ -122,5 +145,11 @@ return `${key} is ${value ? "not " : ""}null`;
   createMemberQuery(key, value, notStr = "") {
-    if (!value.length)
-      return notStr ? "1" : "0";
-    return `${key}${notStr} in (${value.map((val) => this.escape(val)).join(", ")})`;
+    if (Array.isArray(value)) {
+      if (!value.length)
+        return notStr ? "1" : "0";
+      return `${key}${notStr} in (${value.map((val) => this.escape(val)).join(", ")})`;
+    } else {
+      const res = this.jsonContains(this.parseEval(value, false), this.jsonQuote(key, true));
+      this.state.sqlType = "raw";
+      return notStr ? this.logicalNot(res) : res;
+    }
   }
@@ -131,3 +160,8 @@ createRegExpQuery(key, value) {
   createElementQuery(key, value) {
-    return `find_in_set(${this.escape(value)}, ${key})`;
+    var _a;
+    if (((_a = this.state.sqlTypes) == null ? void 0 : _a[this.unescapeId(key)]) === "json") {
+      return this.jsonContains(key, this.quote(JSON.stringify(value)));
+    } else {
+      return `find_in_set(${this.escape(value)}, ${key})`;
+    }
   }
@@ -161,2 +195,49 @@ comparator(operator) {
   }
+  jsonLength(value) {
+    return `json_length(${value})`;
+  }
+  jsonContains(obj, value) {
+    return `json_contains(${obj}, ${value})`;
+  }
+  jsonUnquote(value, pure = false) {
+    if (pure)
+      return `json_unquote(${value})`;
+    const res = this.state.sqlType === "json" ? `json_unquote(${value})` : value;
+    this.state.sqlType = "raw";
+    return res;
+  }
+  jsonQuote(value, pure = false) {
+    if (pure)
+      return `cast(${value} as json)`;
+    const res = this.state.sqlType === "raw" ? `cast(${value} as json)` : value;
+    this.state.sqlType = "json";
+    return res;
+  }
+  createAggr(expr, aggr, nonaggr) {
+    if (this.state.group) {
+      this.state.group = false;
+      const value = aggr(this.parseEval(expr, false));
+      this.state.group = true;
+      this.state.sqlType = "raw";
+      return value;
+    } else {
+      const value = this.parseEval(expr, false);
+      const res = nonaggr ? nonaggr(value) : `(select ${aggr(`json_unquote(${escapeId("value")})`)} from json_table(${value}, '$[*]' columns (value json path '$')) ${(0, import_core.randomId)()})`;
+      this.state.sqlType = "raw";
+      return res;
+    }
+  }
+  groupObject(fields) {
+    const parse = /* @__PURE__ */ __name((expr) => {
+      const value = this.parseEval(expr, false);
+      return this.state.sqlType === "json" ? `json_extract(${value}, '$')` : `${value}`;
+    }, "parse");
+    const res = `json_object(` + Object.entries(fields).map(([key, expr]) => `'${key}', ${parse(expr)}`).join(",") + `)`;
+    this.state.sqlType = "json";
+    return res;
+  }
+  groupArray(value) {
+    this.state.sqlType = "json";
+    return `ifnull(json_arrayagg(${value}), json_array())`;
+  }
   parseFieldQuery(key, query) {
@@ -199,2 +280,3 @@ const conditions = [];
   parseEvalExpr(expr) {
+    this.state.sqlType = "raw";
     for (const key in expr) {
@@ -207,17 +289,20 @@ if (key in this.evalOperators) {
   }
-  parseAggr(expr) {
-    if (typeof expr === "string") {
-      return this.getRecursive(expr);
-    }
-    return this.parseEvalExpr(expr);
+  transformJsonField(obj, path) {
+    this.state.sqlType = "json";
+    return `json_extract(${obj}, '$${path}')`;
   }
-  transformKey(key, fields, prefix) {
-    if (key in fields || !key.includes("."))
+  transformKey(key, fields, prefix, fullKey) {
+    var _a, _b;
+    if (key in fields || !key.includes(".")) {
+      if (((_a = this.state.sqlTypes) == null ? void 0 : _a[key]) || ((_b = this.state.sqlTypes) == null ? void 0 : _b[fullKey])) {
+        this.state.sqlType = this.state.sqlTypes[key] || this.state.sqlTypes[fullKey];
+      }
       return prefix + escapeId(key);
+    }
     const field = Object.keys(fields).find((k) => key.startsWith(k + ".")) || key.split(".")[0];
     const rest = key.slice(field.length + 1).split(".");
-    return `json_unquote(json_extract(${prefix} ${escapeId(field)}, '$${rest.map((key2) => `."${key2}"`).join("")}'))`;
+    return this.transformJsonField(`${prefix} ${escapeId(field)}`, rest.map((key2) => `."${key2}"`).join(""));
   }
   getRecursive(args) {
-    var _a, _b, _c, _d;
+    var _a, _b, _c, _d, _e;
     if (typeof args === "string") {
@@ -228,13 +313,21 @@ return this.getRecursive(["_", args]);
     const fields = ((_b = (_a = this.tables) == null ? void 0 : _a[table]) == null ? void 0 : _b.fields) || {};
-    if ((_c = fields[key]) == null ? void 0 : _c.expr) {
-      return this.parseEvalExpr((_d = fields[key]) == null ? void 0 : _d.expr);
+    const fkey = Object.keys(fields).find((field) => key === field || key.startsWith(field + "."));
+    if (fkey && ((_c = fields[fkey]) == null ? void 0 : _c.expr)) {
+      if (key === fkey) {
+        return this.parseEvalExpr((_d = fields[fkey]) == null ? void 0 : _d.expr);
+      } else {
+        const field = this.parseEvalExpr((_e = fields[fkey]) == null ? void 0 : _e.expr);
+        const rest = key.slice(fkey.length + 1).split(".");
+        return this.transformJsonField(`${field}`, rest.map((key2) => `."${key2}"`).join(""));
+      }
     }
     const prefix = !this.tables || table === "_" || key in fields || Object.keys(this.tables).length === 1 && table in this.tables ? "" : `${escapeId(table)}.`;
-    return this.transformKey(key, fields, prefix);
+    return this.transformKey(key, fields, prefix, `${table}.${key}`);
   }
-  parseEval(expr) {
+  parseEval(expr, unquote = true) {
+    this.state.sqlType = "raw";
     if (typeof expr === "string" || typeof expr === "number" || typeof expr === "boolean" || expr instanceof Date) {
       return this.escape(expr);
     }
-    return this.parseEvalExpr(expr);
+    return unquote ? this.jsonUnquote(this.parseEvalExpr(expr)) : this.parseEvalExpr(expr);
   }
@@ -244,3 +337,3 @@ suffix(modifier) {
     let sql = "";
-    if (group.length) {
+    if (group == null ? void 0 : group.length) {
       sql += ` GROUP BY ${group.map(escapeId).join(", ")}`;
@@ -262,17 +355,11 @@ const filter = this.parseEval(having);
   }
-  get(sel, inline = false) {
+  get(sel, inline = false, group = false) {
     var _a;
     const { args, table, query, ref, model } = sel;
-    const filter = this.parseQuery(query);
-    if (filter === "0")
-      return;
-    const fields = (_a = args[0].fields) != null ? _a : Object.fromEntries(Object.entries(model.fields).filter(([, field]) => !field.deprecated).map(([key]) => [key, { $: [ref, key] }]));
-    const keys = Object.entries(fields).map(([key, value]) => {
-      key = escapeId(key);
-      value = this.parseEval(value);
-      return key === value ? key : `${value} AS ${key}`;
-    }).join(", ");
     let prefix;
     if (typeof table === "string") {
       prefix = escapeId(table);
+      this.state.sqlTypes = Object.fromEntries(Object.entries(model.fields).map(([key, field]) => {
+        return [key, field.type === "json" ? "json" : field.type === "list" ? "list" : "raw"];
+      }));
     } else if (table instanceof import_core.Selection) {
@@ -283,5 +370,10 @@ prefix = this.get(table, true);
     } else {
+      const sqlTypes2 = {};
       prefix = Object.entries(table).map(([key, table2]) => {
         if (typeof table2 !== "string") {
-          return `${this.get(table2, true)} AS ${escapeId(key)}`;
+          const t = `${this.get(table2, true)} AS ${escapeId(key)}`;
+          for (const [fieldKey, fieldType] of Object.entries(this.state.sqlTypes)) {
+            sqlTypes2[`${key}.${fieldKey}`] = fieldType;
+          }
+          return t;
         } else {
@@ -291,2 +383,3 @@ return key === table2 ? escapeId(table2) : `${escapeId(table2)} AS ${escapeId(key)}`;
       }).join(" JOIN ");
+      this.state.sqlTypes = sqlTypes2;
       const filter2 = this.parseEval(args[0].having);
@@ -296,3 +389,15 @@ if (filter2 !== "1")
     }
+    const filter = this.parseQuery(query);
+    if (filter === "0")
+      return;
+    this.state.group = group || !!args[0].group;
+    const sqlTypes = {};
+    const fields = (_a = args[0].fields) != null ? _a : Object.fromEntries(Object.entries(model.fields).filter(([, field]) => !field.deprecated).map(([key]) => [key, { $: [ref, key] }]));
+    const keys = Object.entries(fields).map(([key, value]) => {
+      value = this.parseEval(value, false);
+      sqlTypes[key] = this.state.sqlType;
+      return escapeId(key) === value ? escapeId(key) : `${value} AS ${escapeId(key)}`;
+    }).join(", ");
     let suffix = this.suffix(args[0]);
+    this.state.sqlTypes = sqlTypes;
     if (filter !== "1") {
@@ -322,2 +427,7 @@ suffix = ` WHERE ${filter}` + suffix;
   load(model, obj) {
+    var _a;
+    if (!obj) {
+      const converter = this.types[this.state.sqlType];
+      return converter ? converter.load(model) : model;
+    }
     const result = {};
@@ -328,3 +438,3 @@ for (const key in obj) {
       const { type, initial } = model.fields[key];
-      const converter = this.types[type];
+      const converter = ((_a = this.state.sqlTypes) == null ? void 0 : _a[key]) === "raw" ? this.types[type] : this.types[this.state.sqlTypes[key]];
       result[key] = converter ? converter.load(obj[key], initial) : obj[key];
@@ -331,0 +441,0 @@ }

package.json

 {
   "name": "@minatojs/sql-utils",
-  "version": "4.0.7",
+  "version": "4.1.0",
   "description": "SQL Utilities for Minato",
@@ -33,3 +33,3 @@ "main": "lib/index.js",
   "peerDependencies": {
-    "@minatojs/core": "^2.4.3"
+    "@minatojs/core": "^2.5.0"
   },
@@ -39,2 +39,2 @@ "dependencies": {
   }
-}
+}

src/index.ts

 import { Dict, isNullable } from 'cosmokit'
-import { Eval, Field, isComparable, Model, Modifier, Query, Selection } from '@minatojs/core'
+import { Eval, Field, isComparable, Model, Modifier, Query, randomId, Selection } from '@minatojs/core'
 
@@ -24,2 +24,10 @@ export function escapeId(value: string) {
 
+type SQLType = 'raw' | 'json' | 'list'
+
+interface State {
+  sqlType?: SQLType
+  sqlTypes?: Dict<SQLType>
+  group?: boolean
+}
+
 export class Builder {
@@ -32,2 +40,3 @@ protected escapeMap = {}
   protected evalOperators: EvalOperators
+  protected state: State = {}
 
@@ -78,3 +87,7 @@ constructor(public tables?: Dict<Model>) {
         if (!value) return this.logicalNot(key)
-        return `${key} AND LENGTH(${key}) - LENGTH(REPLACE(${key}, ${this.escape(',')}, ${this.escape('')})) = ${this.escape(value)} - 1`
+        if (this.state.sqlTypes?.[this.unescapeId(key)] === 'json') {
+          return `${this.jsonLength(key)} = ${this.escape(value)}`
+        } else {
+          return `${key} AND LENGTH(${key}) - LENGTH(REPLACE(${key}, ${this.escape(',')}, ${this.escape('')})) = ${this.escape(value)} - 1`
+        }
       },
@@ -111,11 +124,31 @@ }
 
+      // membership
+      $in: ([key, value]) => this.createMemberQuery(this.parseEval(key), value, ''),
+      $nin: ([key, value]) => this.createMemberQuery(this.parseEval(key), value, ' NOT'),
+
       // aggregation
-      $sum: (expr) => `ifnull(sum(${this.parseAggr(expr)}), 0)`,
-      $avg: (expr) => `avg(${this.parseAggr(expr)})`,
-      $min: (expr) => `min(${this.parseAggr(expr)})`,
-      $max: (expr) => `max(${this.parseAggr(expr)})`,
-      $count: (expr) => `count(distinct ${this.parseAggr(expr)})`,
+      $sum: (expr) => this.createAggr(expr, value => `ifnull(sum(${value}), 0)`),
+      $avg: (expr) => this.createAggr(expr, value => `avg(${value})`),
+      $min: (expr) => this.createAggr(expr, value => `(0+min(${value}))`),
+      $max: (expr) => this.createAggr(expr, value => `(0+max(${value}))`),
+      $count: (expr) => this.createAggr(expr, value => `count(distinct ${value})`),
+      $length: (expr) => this.createAggr(expr, value => `count(${value})`, value => {
+        if (this.state.sqlType === 'json') {
+          this.state.sqlType = 'raw'
+          return `${this.jsonLength(value)}`
+        } else {
+          this.state.sqlType = 'raw'
+          return `if(${value}, LENGTH(${value}) - LENGTH(REPLACE(${value}, ${this.escape(',')}, ${this.escape('')})) + 1, 0)`
+        }
+      }),
+
+      $object: (fields) => this.groupObject(fields),
+      $array: (expr) => this.groupArray(this.parseEval(expr, false)),
     }
   }
 
+  protected unescapeId(value: string) {
+    return value.slice(1, value.length - 1)
+  }
+
   protected createNullQuery(key: string, value: boolean) {
@@ -125,5 +158,11 @@ return `${key} is ${value ? 'not ' : ''}null`
 
-  protected createMemberQuery(key: string, value: any[], notStr = '') {
-    if (!value.length) return notStr ? '1' : '0'
-    return `${key}${notStr} in (${value.map(val => this.escape(val)).join(', ')})`
+  protected createMemberQuery(key: string, value: any, notStr = '') {
+    if (Array.isArray(value)) {
+      if (!value.length) return notStr ? '1' : '0'
+      return `${key}${notStr} in (${value.map(val => this.escape(val)).join(', ')})`
+    } else {
+      const res = this.jsonContains(this.parseEval(value, false), this.jsonQuote(key, true))
+      this.state.sqlType = 'raw'
+      return notStr ? this.logicalNot(res) : res
+    }
   }
@@ -136,3 +175,7 @@
   protected createElementQuery(key: string, value: any) {
-    return `find_in_set(${this.escape(value)}, ${key})`
+    if (this.state.sqlTypes?.[this.unescapeId(key)] === 'json') {
+      return this.jsonContains(key, this.quote(JSON.stringify(value)))
+    } else {
+      return `find_in_set(${this.escape(value)}, ${key})`
+    }
   }
@@ -168,2 +211,55 @@
 
+  protected jsonLength(value: string) {
+    return `json_length(${value})`
+  }
+
+  protected jsonContains(obj: string, value: string) {
+    return `json_contains(${obj}, ${value})`
+  }
+
+  protected jsonUnquote(value: string, pure: boolean = false) {
+    if (pure) return `json_unquote(${value})`
+    const res = this.state.sqlType === 'json' ? `json_unquote(${value})` : value
+    this.state.sqlType = 'raw'
+    return res
+  }
+
+  protected jsonQuote(value: string, pure: boolean = false) {
+    if (pure) return `cast(${value} as json)`
+    const res = this.state.sqlType === 'raw' ? `cast(${value} as json)` : value
+    this.state.sqlType = 'json'
+    return res
+  }
+
+  protected createAggr(expr: any, aggr: (value: string) => string, nonaggr?: (value: string) => string) {
+    if (this.state.group) {
+      this.state.group = false
+      const value = aggr(this.parseEval(expr, false))
+      this.state.group = true
+      this.state.sqlType = 'raw'
+      return value
+    } else {
+      const value = this.parseEval(expr, false)
+      const res = nonaggr ? nonaggr(value)
+        : `(select ${aggr(`json_unquote(${escapeId('value')})`)} from json_table(${value}, '$[*]' columns (value json path '$')) ${randomId()})`
+      this.state.sqlType = 'raw'
+      return res
+    }
+  }
+
+  protected groupObject(fields: any) {
+    const parse = (expr) => {
+      const value = this.parseEval(expr, false)
+      return this.state.sqlType === 'json' ? `json_extract(${value}, '$')` : `${value}`
+    }
+    const res = `json_object(` + Object.entries(fields).map(([key, expr]) => `'${key}', ${parse(expr)}`).join(',') + `)`
+    this.state.sqlType = 'json'
+    return res
+  }
+
+  protected groupArray(value: string) {
+    this.state.sqlType = 'json'
+    return `ifnull(json_arrayagg(${value}), json_array())`
+  }
+
   protected parseFieldQuery(key: string, query: Query.FieldExpr) {
@@ -214,2 +310,3 @@ const conditions: string[] = []
   private parseEvalExpr(expr: any) {
+    this.state.sqlType = 'raw'
     for (const key in expr) {
@@ -223,14 +320,17 @@ if (key in this.evalOperators) {
 
-  private parseAggr(expr: any) {
-    if (typeof expr === 'string') {
-      return this.getRecursive(expr)
-    }
-    return this.parseEvalExpr(expr)
+  protected transformJsonField(obj: string, path: string) {
+    this.state.sqlType = 'json'
+    return `json_extract(${obj}, '$${path}')`
   }
 
-  private transformKey(key: string, fields: {}, prefix: string) {
-    if (key in fields || !key.includes('.')) return prefix + escapeId(key)
+  private transformKey(key: string, fields: {}, prefix: string, fullKey: string) {
+    if (key in fields || !key.includes('.')) {
+      if (this.state.sqlTypes?.[key] || this.state.sqlTypes?.[fullKey]) {
+        this.state.sqlType = this.state.sqlTypes[key] || this.state.sqlTypes[fullKey]
+      }
+      return prefix + escapeId(key)
+    }
     const field = Object.keys(fields).find(k => key.startsWith(k + '.')) || key.split('.')[0]
     const rest = key.slice(field.length + 1).split('.')
-    return `json_unquote(json_extract(${prefix} ${escapeId(field)}, '$${rest.map(key => `."${key}"`).join('')}'))`
+    return this.transformJsonField(`${prefix} ${escapeId(field)}`, rest.map(key => `."${key}"`).join(''))
   }
@@ -244,4 +344,11 @@
     const fields = this.tables?.[table]?.fields || {}
-    if (fields[key]?.expr) {
-      return this.parseEvalExpr(fields[key]?.expr)
+    const fkey = Object.keys(fields).find(field => key === field || key.startsWith(field + '.'))
+    if (fkey && fields[fkey]?.expr) {
+      if (key === fkey) {
+        return this.parseEvalExpr(fields[fkey]?.expr)
+      } else {
+        const field = this.parseEvalExpr(fields[fkey]?.expr)
+        const rest = key.slice(fkey.length + 1).split('.')
+        return this.transformJsonField(`${field}`, rest.map(key => `."${key}"`).join(''))
+      }
     }
@@ -251,10 +358,11 @@ const prefix = !this.tables || table === '_' || key in fields
     || (Object.keys(this.tables).length === 1 && table in this.tables) ? '' : `${escapeId(table)}.`
-    return this.transformKey(key, fields, prefix)
+    return this.transformKey(key, fields, prefix, `${table}.${key}`)
   }
 
-  parseEval(expr: any): string {
+  parseEval(expr: any, unquote: boolean = true): string {
+    this.state.sqlType = 'raw'
     if (typeof expr === 'string' || typeof expr === 'number' || typeof expr === 'boolean' || expr instanceof Date) {
       return this.escape(expr)
     }
-    return this.parseEvalExpr(expr)
+    return unquote ? this.jsonUnquote(this.parseEvalExpr(expr)) : this.parseEvalExpr(expr)
   }
@@ -265,3 +373,3 @@
     let sql = ''
-    if (group.length) {
+    if (group?.length) {
       sql += ` GROUP BY ${group.map(escapeId).join(', ')}`
@@ -281,20 +389,12 @@ const filter = this.parseEval(having)
 
-  get(sel: Selection.Immutable, inline = false) {
+  get(sel: Selection.Immutable, inline = false, group = false) {
     const { args, table, query, ref, model } = sel
-    const filter = this.parseQuery(query)
-    if (filter === '0') return
 
     // get prefix
-    const fields = args[0].fields ?? Object.fromEntries(Object
-      .entries(model.fields)
-      .filter(([, field]) => !field!.deprecated)
-      .map(([key]) => [key, { $: [ref, key] }]))
-    const keys = Object.entries(fields).map(([key, value]) => {
-      key = escapeId(key)
-      value = this.parseEval(value)
-      return key === value ? key : `${value} AS ${key}`
-    }).join(', ')
     let prefix: string | undefined
     if (typeof table === 'string') {
       prefix = escapeId(table)
+      this.state.sqlTypes = Object.fromEntries(Object.entries(model.fields).map(([key, field]) => {
+        return [key, field!.type === 'json' ? 'json' : field!.type === 'list' ? 'list' : 'raw']
+      }))
     } else if (table instanceof Selection) {
@@ -304,5 +404,10 @@ prefix = this.get(table, true)
     } else {
+      const sqlTypes: Dict<SQLType> = {}
       prefix = Object.entries(table).map(([key, table]) => {
         if (typeof table !== 'string') {
-          return `${this.get(table, true)} AS ${escapeId(key)}`
+          const t = `${this.get(table, true)} AS ${escapeId(key)}`
+          for (const [fieldKey, fieldType] of Object.entries(this.state.sqlTypes!)) {
+            sqlTypes[`${key}.${fieldKey}`] = fieldType
+          }
+          return t
         } else {
@@ -312,2 +417,3 @@ return key === table ? escapeId(table) : `${escapeId(table)} AS ${escapeId(key)}`
       }).join(' JOIN ')
+      this.state.sqlTypes = sqlTypes
       const filter = this.parseEval(args[0].having)
@@ -317,4 +423,21 @@ if (filter !== '1') prefix += ` ON ${filter}`
 
+    const filter = this.parseQuery(query)
+    if (filter === '0') return
+
+    this.state.group = group || !!args[0].group
+    const sqlTypes: Dict<SQLType> = {}
+    const fields = args[0].fields ?? Object.fromEntries(Object
+      .entries(model.fields)
+      .filter(([, field]) => !field!.deprecated)
+      .map(([key]) => [key, { $: [ref, key] }]))
+    const keys = Object.entries(fields).map(([key, value]) => {
+      value = this.parseEval(value, false)
+      sqlTypes[key] = this.state.sqlType!
+      return escapeId(key) === value ? escapeId(key) : `${value} AS ${escapeId(key)}`
+    }).join(', ')
+
     // get suffix
     let suffix = this.suffix(args[0])
+    this.state.sqlTypes = sqlTypes
+
     if (filter !== '1') {
@@ -349,3 +472,10 @@ suffix = ` WHERE ${filter}` + suffix
 
-  load(model: Model, obj: any): any {
+  load(obj: any): any
+  load(model: Model, obj: any): any
+  load(model: any, obj?: any) {
+    if (!obj) {
+      const converter = this.types[this.state.sqlType!]
+      return converter ? converter.load(model) : model
+    }
+
     const result = {}
@@ -355,3 +485,3 @@ for (const key in obj) {
       const { type, initial } = model.fields[key]!
-      const converter = this.types[type]
+      const converter = this.state.sqlTypes?.[key] === 'raw' ? this.types[type] : this.types[this.state.sqlTypes![key]]
       result[key] = converter ? converter.load(obj[key], initial) : obj[key]
@@ -358,0 +488,0 @@ }

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Native code

Supply chain risk

Contains native code (e.g., compiled binaries or shared libraries). Including native code can obscure malicious behavior.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

54179

increased by18.31%

Lines of code

982

increased by32.52%

Number of medium supply chain risk alerts

0

decreased by-100%

Worsened metrics

Number of package files

6

decreased by-14.29%

No dependency changes