Skip to content

JSON type usage

This is a memo guide for keeping in mind what is possible when manipulating JSON data with TypeORM and various databases: SQL Server (MSSQL), PostgreSQL, MySQL.

TypeORM + MSSQL with simple-json column type

how to use:

  • declaration
@Column("simple-json")
value: { [x: string]: any };

info:

  • uses MSSQL column type ntext by default (no way to change it)

  • typeorm framework uses JSON.stringify and JSON.parse to put in the db (hidden)

  • plain JSON is stored as text in database

issues:

  • cannot use the MSSQL JSON functions on ntext (only on nvarchar), else this error happens:

https://stackoverflow.com/questions/56111730/argument-data-type-ntext-is-invalid-for-argument-1-of-isjson-function#comment98857077_56111730 :

"You could CAST as nvarchar(MAX). Better to change the schema to nvarchar(MAX) given ntext has been deprecated for nearly 15 years"

TypeORM + MSSQL with the MSSQL "json"

how to use:

@Column({ type: "text", transformer: { from: JSON.parse, to: JSON.stringify } })
value: { [x: string]: any };

info:

  • MSSQL does not have a JSON column type, it uses nvarchar: https://www.c-sharpcorner.com/article/working-with-json-in-sql-server-2016/

  • there are mssql functions that can manipulate JSON data: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16

  • plain JSON is stored as text in database

use JSON functions in code:

  • can call MSSQL JSON functions in the query builder (not related to typeorm, very mssql specific):
const qb = this.desiredConfigurationRepository.createQueryBuilder("desired");
qb.select("JSON_VALUE(value, '$.high') AS highValue");
qb.addSelect("JSON_VALUE(value, '$.low') AS lowValue");
qb.where("ISJSON(value) = 1");
const res = await qb.getRawMany();
console.log(res);

logs:

query: SELECT JSON_VALUE(value, '$.high') AS highValue, JSON_VALUE(value, '$.low') AS lowValue FROM "desired_configuration" "desired" WHERE ISJSON(value) = 1
[ { highValue: '45', lowValue: '12' } ]

TypeORM + PostgreSQL with json/jsonb

how to use:

@Column("json") // json stores the json as plain text. fast to input, slower on json querying
value: { [x: string]: any };

or

@Column("jsonb") // jsonb stores in a way that input is a bit slower, but json processing is faster
value: { [x: string]: any };

additional info:

https://www.postgresql.org/docs/14/datatype-json.html

use JSON querying in code with typeorm:

  • can query with JSON in the query builder (not related to typeorm, very postgres specific):

(same for json and jsonb)

const qb = this.desiredConfigurationRepository.createQueryBuilder("desired");
qb.select("value -> 'high' as highValue");
qb.where({ deviceId });
const res = await qb.getRawMany();
console.log(res);

logs:

query: SELECT value -> 'high' as highValue FROM "desired_configuration" "desired" WHERE "desired"."deviceId" = $1 -- PARAMETERS: ["6F64872D-777A-491D-B0A5-38093F86D14C"]
[ { highvalue: null }, { highvalue: 45 } ]

TypeORM + MySQL with json

how to use:

@Column("json")
value: { [x: string]: any };

info::

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
  • JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements

https://dev.mysql.com/doc/refman/8.0/en/json.html

use JSON querying in code with typeorm:

  • can query with JSON in the query builder (not related to typeorm, very mysql specific):
const qb = this.desiredConfigurationRepository.createQueryBuilder("desired");
qb.select(`value->>"$.high" as highValue`);
qb.where({ deviceId });
const res = await qb.getRawMany();
console.log(res);

logs:

query: SELECT value->>"$.high" as highValue FROM `desired_configuration` `desired` WHERE `desired`.`deviceId` = ? -- PARAMETERS: ["6F64872D-777A-491D-B0A5-38093F86D14C"]
[
  RowDataPacket { highValue: '45' },
  RowDataPacket { highValue: null }
]