pgsql数据库+java语言的json最佳实践

实体类定义

@TableField(typeHandler = JacksonTypeHandler.class)    
private JsonNode orderDetails;            

数据处理
order.setOrderDetails(objectMapper.readTree(formData.path("tableField_llko50k0").toString()));  // 订单明细子表单 json字段

同时涉及 3 个层面：
👉 MyBatis Plus 的 TypeHandler
👉 Jackson 的 JsonNode
👉 PostgreSQL / JSON 字段映射机制

我按你现在用的技术栈（Spring Boot 3 + MP + PgSQL）给你拆清楚。

---

一、这两句代码各自做了什么

1️⃣ 实体字段声明

@TableField(typeHandler = JacksonTypeHandler.class)
private JsonNode orderDetails;

这句话的含义是：

告诉 MyBatis Plus：这个字段虽然是 JsonNode，但请使用 JacksonTypeHandler 来负责 Java ↔ 数据库 的转换

关键点：

元素	含义
JsonNode	Java 内存中的 JSON 树结构
@TableField(typeHandler=...)	指定“谁来序列化 / 反序列化”
JacksonTypeHandler	MP 内置，基于 Jackson

---

2️⃣ 赋值代码

order.setOrderDetails(
    objectMapper.readTree(
        formData.path("tableField_llko50k0").toString()
    )
);

这一步是：

把前端传来的 JSON 数据 → 解析成 Jackson 的 JsonNode 对象

流程是：

前端 JSON
   ↓
formData (JsonNode)
   ↓ path(...)
String JSON
   ↓ readTree()
JsonNode（orderDetails）

此时：

✅ orderDetails 已经是一个合法的 JsonNode 对象

---

二、为什么 JsonNode 能“匹配”数据库 JSON 字段？

这是核心问题 👍
答案是：TypeHandler 在中间“翻译”

---

三、JacksonTypeHandler 在背后做了什么？

1️⃣ 写入数据库时（INSERT / UPDATE）

MyBatis Plus 调用：

JacksonTypeHandler#setNonNullParameter(...)

等价逻辑是：

// 伪代码
String json = objectMapper.writeValueAsString(orderDetails);
preparedStatement.setObject(i, json);

也就是：

JsonNode
  ↓ Jackson
String(JSON)
  ↓ JDBC
json / jsonb 字段

👉 所以数据库里存的是 标准 JSON 字符串

---

2️⃣ 从数据库读出来时（SELECT）

流程反过来：

String json = resultSet.getString(column);
JsonNode node = objectMapper.readTree(json);

结果：

数据库 json/jsonb
  ↓ String
  ↓ Jackson
JsonNode

---

四、为什么不用 Map / String，而用 JsonNode？

这是你这段代码非常专业的地方 👌

1️⃣ JsonNode 的优势

类型	问题
String	每次都要自己 parse
Map<String,Object>	层级深了很难用
JsonNode	天然支持树结构、path、安全

例如：

orderDetails.path("items").get(0).path("price").asInt();

✔ 不会 NPE
✔ 支持深层结构
✔ 和前端 JSON 结构天然一致

---

2️⃣ 和 PostgreSQL jsonb 非常契合

如果你的字段是：

order_details jsonb

那就是：

层	类型
Java	JsonNode
MyBatis	JacksonTypeHandler
DB	json / jsonb

这是当前 Java + PgSQL 存 JSON 的最佳实践之一

---

五、为什么 MyBatis Plus “认识” JsonNode？

关键不是 MP 认识 JsonNode，而是：

你明确告诉了 MP 用哪个 TypeHandler

如果你写成：

private JsonNode orderDetails;

❌ 不加 typeHandler
MP 不知道怎么存

加了：

@TableField(typeHandler = JacksonTypeHandler.class)

✔ 一切都通了

---

六、常见踩坑提醒（你很可能会遇到）

⚠️ 1. PgSQL 推荐 jsonb

order_details jsonb

性能、索引、查询都更好。

---

⚠️ 2. 查询条件要注意

不能直接：

eq(Order::getOrderDetails, xxx)

要用：

order_details ->> 'field' = 'value'

或 @Select 自定义 SQL。

---

⚠️ 3. 批量插入一定要有 TypeHandler

否则会报：

Can't infer the SQL type to use for an instance of JsonNode

---

七、一句话总结（记住这个）

JsonNode 能匹配数据库 JSON 字段，不是因为它“天生能存”，而是 JacksonTypeHandler 充当了翻译官

Java 内存里是 JsonNode，数据库里永远是 JSON 字符串 / jsonb

---

 

pgsql数据库的json语法最佳实践

我按**“从你写的 SQL 一句一句拆开讲”，不讲空概念，只讲你现在正在用、马上能用的语法**。

下面这条作为主线来讲（你刚刚在用的）：

SELECT
    so.id,
    elem ->> 'textField_lmk4zhzg'    AS material_name,
    (elem ->> 'numberField_llko50k6')::numeric AS qty
FROM yida_data_sync.sales_order so
CROSS JOIN LATERAL jsonb_array_elements(so.order_details) AS elem;

---

一、FROM 子句：为什么这么写？

1️⃣ 表别名 so

FROM yida_data_sync.sales_order so

等价于：

FROM yida_data_sync.sales_order AS so

作用

• 简化字段引用

• 后面 so.order_details、so.id 都靠它

📌 建议：只要表一多，必须起别名

---

二、jsonb_array_elements() —— JSON 数组“拆行神器”

2️⃣ 函数本身

jsonb_array_elements(so.order_details)

含义

• 入参：一个 jsonb 数组

• 出参：多行 jsonb

示例：

[
  {"a":1},
  {"a":2}
]

会变成：

elem

{"a":1}

{"a":2}

---

三、CROSS JOIN LATERAL —— 核心但最容易懵

3️⃣ 先说 JOIN 类型

CROSS JOIN

= 笛卡尔积
👉 每一行左表，匹配右边返回的所有行

---

4️⃣ 再说 LATERAL（重点）

CROSS JOIN LATERAL jsonb_array_elements(so.order_details)

LATERAL 的意思是：

右边的子查询 / 函数
可以引用左边表的字段

没有 LATERAL：

jsonb_array_elements(so.order_details) ❌ 报错

因为普通 JOIN 右表不能用左表字段

---

5️⃣ 用一句人话解释

对 sales_order 的每一行，
把这一行里的 order_details 数组拆成多行

---

6️⃣ 为什么不用老写法？

你可能见过：

FROM sales_order,
     jsonb_array_elements(order_details) elem

这是 隐式 LATERAL（旧写法）
✔ 能用
❌ 可读性差
❌ 复杂 SQL 易踩坑

👉 现在推荐你用 CROSS JOIN LATERAL

---

四、AS elem —— 别名不是装饰品

AS elem

含义：

• 给 jsonb_array_elements(...) 返回的 每一行 JSON 起名叫 elem

后面才能写：

elem ->> 'textField_lmk4zhzg'

---

五、-> 和 ->> 的区别（必须会）

7️⃣ ->：取 JSON

elem -> 'textField_lmk4zhzg'

返回类型：json / jsonb

"外拖贴面胶"

---

8️⃣ ->>：取文本（99% 用它）

elem ->> 'textField_lmk4zhzg'

返回类型：text

外拖贴面胶

📌 规则

• 要参与计算、比较、展示 → 用 ->>

• 继续当 JSON 用 → 用 ->

---

六、::numeric —— PostgreSQL 类型转换

9️⃣ 强制类型转换语法

(elem ->> 'numberField_llko50k6')::numeric

等价于：

CAST(elem ->> 'numberField_llko50k6' AS numeric)

📌 为什么要转？

• ->> 取出来的是 text

• text 不能直接算数

---

七、AS xxx —— 字段起别名

AS material_name

作用：

• 结果集字段名

• Java / MyBatis 直接映射

📌 强烈建议：

SQL 里就把字段名起好
Java 就少一半映射代码

---

八、to_timestamp() —— 你这个时间字段专用

🔟 你数据里的时间

1767024000000

这是 毫秒时间戳

---

正确转换方式

to_timestamp(
  (elem ->> 'dateField_lmk4zhze')::bigint / 1000
)

步骤拆解：

1. ->> 取 text

2. ::bigint 转数字

3. / 1000 转秒

4. to_timestamp 转时间

---

九、整体 SQL 的“执行顺序”（理解用）

1. FROM sales_order so
2. 对每一行执行 jsonb_array_elements(order_details)
3. LATERAL 把拆出来的 elem 关联回当前 so
4. SELECT 取字段
5. 类型转换
6. 输出结果

---

十、最后给你一张“速查表”

语法	含义
jsonb_array_elements()	JSON 数组拆行
CROSS JOIN LATERAL	右边可用左表字段
->	取 JSON
->>	取 text
::numeric	类型转换
AS	起别名

---

十一句真心建议（结合你现在的项目）

JSON 拆行 SQL 一旦写对，就立刻封装成 VIEW
以后你是在“查表”，不是“查 JSON”

如果你愿意，下一步我可以：

• 帮你 逐字段对照你这份 JSON

• 或给你画一张 JSON → SQL → Java DTO 的结构图

你现在问的，已经是数据库进阶段位的问题了 👍