Working with JSON Fields in TypeORM: Using JSON_SET

When working with JSON data in a database, TypeORM provides flexibility but does not natively support JSON functions like JSON_SET. However, there are several effective ways to work with JSON fields using TypeORM. In this blog post, we will explore different approaches for updating JSON fields in a TypeORM-managed database.

Method 1: Using Custom Repository Methods

Custom repository methods are a great way to encapsulate complex logic within a repository. Although this approach still uses raw SQL, it helps keep your codebase organized.

import { EntityRepository, Repository } from 'typeorm';
import { YourEntity } from './YourEntity';

@EntityRepository(YourEntity)
export class YourEntityRepository extends Repository {
  
  async updateJsonField(id: number, key: string, value: any): Promise {
    await this.query(`
      UPDATE your_table
      SET json_column = JSON_SET(json_column, '$.${key}', ?)
      WHERE id = ?
    `, [value, id]);
  }
}
        

In this method, you define a custom repository and create an updateJsonField method that uses raw SQL to update the JSON field.

Method 2: Using QueryBuilder with Raw SQL

TypeORM's QueryBuilder allows you to mix raw SQL with TypeORM's query-building capabilities, providing a more flexible approach.

import { getRepository } from 'typeorm';
import { YourEntity } from './YourEntity';

async function updateJsonField() {
  const repository = getRepository(YourEntity);

  await repository.createQueryBuilder()
    .update()
    .set({
      json_column: () => `JSON_SET(json_column, '$.yourKey', 'newValue')`
    })
    .where('id = :id', { id: yourId })
    .execute();
}
        

Here, you use the QueryBuilder to execute raw SQL for updating the JSON field, while still benefiting from TypeORM’s query building.

Method 3: Updating Entity and Saving Changes

If you prefer to avoid raw SQL entirely, you can fetch the entity, modify the JSON field in your application code, and then save the changes.

import { getRepository } from 'typeorm';
import { YourEntity } from './YourEntity';

async function updateJsonField() {
  const repository = getRepository(YourEntity);
  
  // Fetch the entity
  const entity = await repository.findOne({ where: { id: yourId } });
  if (entity) {
    // Modify the JSON field
    entity.json_column.yourKey = 'newValue';

    // Save the changes
    await repository.save(entity);
  }
}
        

This approach involves fetching the entity, modifying its JSON field in your application code, and saving the updated entity. It avoids using raw SQL but may require additional logic to handle the JSON updates.

Conclusion

Each method for working with JSON fields in TypeORM has its own advantages:

• Custom Repository Methods: Encapsulate raw SQL logic.
• QueryBuilder with Raw SQL: Provides flexibility while using TypeORM’s query capabilities.
• Updating Entity and Saving Changes: Avoids raw SQL by handling JSON updates in application code.

Choose the method that best fits your needs and coding style. By leveraging these approaches, you can efficiently manage JSON data within your TypeORM applications.