如何存储JSON？从VK获取帖子存入PostgreSQL及后端过滤咨询

一、PostgreSQL存储JSON数据的方式 #

PostgreSQL提供两种原生JSON类型：

• json：存储原始JSON文本，查询时实时解析，适合写入频繁、查询较少的场景。
• jsonb：以二进制格式存储，支持索引、快速查询，适合需要频繁基于JSON内容做筛选的场景，推荐使用。

你可以选择直接存储完整的VK帖子JSON结构，或者拆分常用字段（如帖子ID、发布时间、作者ID）单独存储，同时保留完整JSON数据用于备份。

二、实现PostgreSQL数据入库（基于NestJS + TypeORM） #

1. 创建数据库实体 #

先定义对应的数据表结构，同时支持完整JSON存储和字段拆分：

// src/posts/entities/vk-post.entity.ts
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity('vk_posts')
export class VkPost {
  @PrimaryGeneratedColumn()
  id: number;

  // 存储完整的VK帖子JSON数据
  @Column('jsonb')
  rawData: Record<string, any>;

  // 拆分常用字段，方便快速查询和过滤
  @Column({ unique: true })
  postId: number; // VK帖子自身ID

  @Column()
  ownerId: number; // 发布者ID

  @Column({ type: 'timestamp' })
  publishedAt: Date; // 发布时间
}

2. 配置TypeORM连接 #

在app.module.ts中配置PostgreSQL连接，并注册实体：

// src/app.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { AppService } from './app.service';
import { VkPost } from './posts/entities/vk-post.entity';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'your_username',
      password: 'your_password',
      database: 'your_db_name',
      entities: [VkPost],
      synchronize: true, // 开发环境可用，生产建议关闭手动迁移
    }),
    TypeOrmModule.forFeature([VkPost]),
  ],
  providers: [AppService],
})
export class AppModule {}

3. 修改服务类实现入库逻辑 #

更新你的AppService，注入仓库并完成数据获取、过滤、入库流程：

@Injectable()
export class AppService {
  private readonly logger = new Logger(AppService.name);
  
  constructor(
    private readonly httpService: HttpService,
    private readonly vkPostRepository: Repository<VkPost>,
  ) {}

  async fetchAndSavePosts(): Promise<{ filteredCount: number; savedCount: number }> {
    try {
      // 调用VK API获取数据
      const { data } = await firstValueFrom(
        this.httpService.get<any>(
          'https://api.vk.com/method/wall.get?owner_id=-198838959&count=5&offset=10&access_token=08ed89a008ed89a008ed89a0f00bf82736008ed08ed89a06de41f5d7e95a7d62639387a&v=5.131'
        )
      );

      const rawPosts = data.response?.items || [];
      if (!rawPosts.length) {
        return { filteredCount: 0, savedCount: 0 };
      }

      // 入库前过滤逻辑（示例：过滤7天内的帖子）
      const sevenDaysAgo = Date.now() - 7 * 24 * 60 * 60 * 1000;
      const filteredPosts = rawPosts.filter(post => {
        const postTimestamp = post.date * 1000;
        return postTimestamp >= sevenDaysAgo;
      });

      // 准备入库数据
      const postsToSave = filteredPosts.map(post => ({
        rawData: post,
        postId: post.id,
        ownerId: post.owner_id,
        publishedAt: new Date(post.date * 1000), // VK返回的date是秒级时间戳
      }));

      // 批量保存（自动跳过重复postId）
      await this.vkPostRepository.save(postsToSave, { chunk: 10 });

      return { filteredCount: filteredPosts.length, savedCount: postsToSave.length };
    } catch (error: any) {
      this.logger.error(error.response?.data || error.message);
      throw new Error('Failed to fetch or save VK posts');
    }
  }
}

三、后端实现帖子过滤 #

过滤逻辑可以放在入库前（减少入库数据量）或查询时（灵活筛选已存储数据），以下是常见场景示例：

1. 入库前过滤扩展示例 #

// 过滤包含指定关键词的帖子
const keywordFiltered = filteredPosts.filter(post => 
  post.text?.toLowerCase().includes('your_keyword')
);

// 过滤带图片的帖子
const imageFiltered = filteredPosts.filter(post => 
  post.attachments?.some(att => att.type === 'photo')
);

// 过滤置顶帖子
const pinnedFiltered = filteredPosts.filter(post => post.is_pinned === 1);

2. 查询时过滤示例（基于已存储数据） #

利用TypeORM的QueryBuilder结合PostgreSQL的jsonb操作：

// 查询包含指定关键词的帖子
async getPostsWithKeyword(keyword: string): Promise<VkPost[]> {
  return this.vkPostRepository
    .createQueryBuilder('post')
    .where('post.rawData->>:field LIKE :keyword', {
      field: 'text',
      keyword: `%${keyword.toLowerCase()}%`,
    })
    .getMany();
}

// 查询最近30天发布的帖子
async getRecentPosts(): Promise<VkPost[]> {
  const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
  return this.vkPostRepository
    .createQueryBuilder('post')
    .where('post.publishedAt >= :date', { date: thirtyDaysAgo })
    .getMany();
}

内容的提问来源于stack exchange，提问作者Максим Дмитриевич