如何在PostgreSQL中按jsonb内id查询数据并编写Express GET接口

一、PostgreSQL 侧查询实现 #

注意：你提供的测试数据插入语句存在多余单引号、JSON结构未闭合的语法错误，执行前需要修正，修正后的可执行建表、插入语句如下：

-- 建表语句（原语句无问题，可直接执行）
CREATE TABLE public.users 
(
    id serial PRIMARY KEY, 
    users jsonb
);

-- 修正后的测试数据插入语句
INSERT INTO users (users)
VALUES
    (
        '[
{
"id": 1,
"name": "Leanne Graham",
"username": "Bret",
"email": "Sincere@april.biz",
"address": {},
"phone": "1-770-736-8031 x56442",
"website": "hildegard.org",
"company": {
"name": "Romaguera-Crona",
"catchPhrase": "Multi-layered client-server neural-net",
"bs": "harness real-time e-markets"
}
},
{
"id": 2,
"name": "Ervin Howell",
"username": "Antonette",
"email": "Shanna@melissa.tv",
"address": {
"street": "Victor Plains",
"suite": "Suite 879",
"city": "Wisokyburgh",
"zipcode": "90566-7771",
"geo": {
"lat": "-43.9509",
"lng": "-34.4618"
}
}
]'
    );

核心查询逻辑：通过jsonb_array_elements函数将存储用户数组的jsonb字段拆分为独立的用户对象行，再匹配对象内的id属性即可拿到目标数据，查询SQL如下：

-- 示例：查询jsonb数组中id为1的用户对象
SELECT elem AS target_user
FROM public.users,
     jsonb_array_elements(users) AS elem
WHERE (elem -> 'id')::int = 1;

语法说明：

• jsonb_array_elements(users)：将users字段存储的JSON数组逐行展开，每行对应数组内的一个用户对象
• (elem -> 'id')::int = 1：提取用户对象的id属性并转为整数类型，和目标id做精确匹配，避免字符串类型隐式转换导致的匹配异常
• 如果查询频率较高，可以给users字段创建GIN索引提升检索效率，索引语句参考：

CREATE INDEX idx_users_jsonb ON public.users USING GIN(users jsonb_path_ops);

二、Express 框架 GET 接口实现 #

前置依赖安装 #

项目初始化后安装所需依赖包：

npm init -y
npm install express pg

pg是PostgreSQL官方提供的Node.js客户端，用于连接数据库执行SQL。

接口代码实现 #

新建server.js文件，写入以下代码，数据库连接配置按实际环境修改即可：

const express = require('express');
const { Pool } = require('pg');
const app = express();
const PORT = 3000;

// 初始化PostgreSQL连接池
const pgPool = new Pool({
  user: '你的数据库用户名',
  host: '127.0.0.1',
  database: '你的数据库名',
  password: '你的数据库密码',
  port: 5432,
  // 可根据业务场景调整连接池最大连接数
  max: 10
});

// 用户查询GET接口，通过query参数传userId，示例请求：GET /user?userId=1
app.get('/user', async (req, res) => {
  try {
    // 参数校验
    const userId = req.query.userId;
    if (!userId || !Number.isInteger(Number(userId))) {
      return res.status(400).json({
        code: 400,
        msg: '参数非法：userId需传入整数'
      });
    }

    // 参数化查询，避免SQL注入
    const sql = `
      SELECT elem AS userInfo
      FROM public.users,
           jsonb_array_elements(users) AS elem
      WHERE (elem -> 'id')::int = $1
    `;
    const { rows } = await pgPool.query(sql, [Number(userId)]);

    if (rows.length === 0) {
      return res.status(404).json({
        code: 404,
        msg: '未查询到对应用户信息'
      });
    }

    // 返回结果
    return res.status(200).json({
      code: 200,
      msg: '查询成功',
      data: rows[0].userinfo
    });
  } catch (error) {
    console.error('用户查询接口异常：', error);
    return res.status(500).json({
      code: 500,
      msg: '服务内部错误'
    });
  }
});

// 启动服务
app.listen(PORT, () => {
  console.log(`服务运行在 http://localhost:${PORT}`);
});

启动测试 #

执行命令启动服务：

node server.js

服务启动后，访问http://localhost:3000/user?userId=1即可拿到id=1的用户JSON数据。

内容的提问来源于stack exchange，提问作者Naimur Rahman D