时间戳

首页 > 解决方案 > 如何在虚拟数据类型中使用 JSONB 数据？

javascript - 如何在虚拟数据类型中使用 JSONB 数据？

问题描述

我在我的模型中定义了以下对象：

result: {
  type: DataTypes.JSONB,
  defaultValue: {},
  jsonSchema: {
    schema: {
      type: 'object'
    }
  }
}

此对象中存储的 json 数据如下所示：

"result": {
    "score": {
        "scaled": 1,
        "raw": 8,
        "min": 7,
        "max": 10
    },
    "success": true,
    "completion": true,
    "duration": "2H30M"
}

现在我尝试创建一个值为 off 成功的虚拟字段。我试过这样做，但没有任何运气。

result_success: {
  type: DataTypes.VIRTUAL,
  get () {
    return this.getDataValue('result').success;
  }
},

但是当我通过这个模型提出请求时，这不会被返回。

标签： javascriptnode.jspostgresqlsequelize.js

解决方案

这个对我有用。这是一个例子：

index.ts：

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';
import assert from 'assert';

class SomeEntity extends Model {
  public id!: number;
  public result!: any;
  public result_success!: boolean;
}
SomeEntity.init(
  {
    result: {
      type: DataTypes.JSONB,
      defaultValue: {},
    },
    result_success: {
      type: DataTypes.VIRTUAL,
      get(this: SomeEntity) {
        return this.getDataValue('result').success;
      },
    },
  },
  { sequelize, modelName: 'SomeEntities' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    const entity = await SomeEntity.create({
      result: {
        score: { scaled: 1, raw: 8, min: 7, max: 10 },
        success: true,
        completion: true,
        duration: '2H30M',
      },
    });
    assert(entity.result_success, 'The value of the virtual field result_success should be truthy');
    console.log('entity.result_success: ', entity.result_success);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

执行上述代码后，查看数据库中的数据记录：

node-sequelize-examples=# select * from "SomeEntities";
 id |                                                      result
----+-------------------------------------------------------------------------------------------------------------------
  1 | {"score": {"max": 10, "min": 7, "raw": 8, "scaled": 1}, "success": true, "duration": "2H30M", "completion": true}
(1 row)

并打印日志，entity.result_success虚拟字段true按预期返回：

{ POSTGRES_HOST: '127.0.0.1',
  POSTGRES_PORT: '5430',
  POSTGRES_PASSWORD: 'testpass',
  POSTGRES_USER: 'testuser',
  POSTGRES_DB: 'node-sequelize-examples' }
Executing (default): DROP TABLE IF EXISTS "SomeEntities" CASCADE;
Executing (default): DROP TABLE IF EXISTS "SomeEntities" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "SomeEntities" ("id"   SERIAL , "result" JSONB DEFAULT '{}', PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'SomeEntities' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "SomeEntities" ("id","result") VALUES (DEFAULT,$1) RETURNING *;
entity.result_success:  true

源代码：https ://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/59877140

推荐阅读