sql提取 JSON 字段的文本值

直接上例子！：

sql创建表及查询sql如下：

CREATE TABLE queue_consumer_exeception_record (
    _id SERIAL PRIMARY KEY,
    message JSON NOT NULL,
    create_date TIMESTAMP NOT NULL,
    status INT NOT NULL,
    queue_name VARCHAR(100)
);

INSERT INTO queue_consumer_exeception_record (message, create_date, status, queue_name)
VALUES
    ('{"pre_order_number": "PO12345"}', '2024-05-08 12:00:00', 0, 'yinnidai_risk_credit_result'),
    ('{"pre_order_number": "PO67890"}', '2024-05-07 15:00:00', 0, 'yinnidai_risk_credit_result'),
    ('{"pre_order_number": "PO24680"}', '2024-05-09 08:00:00', 1, 'yinnidai_risk_credit_result');

-- 查询语句
select message->>'$.pre_order_number'
from queue_consumer_exeception_record
where create_date > '2024-05-07 00:00:00'
  and `status` = 0
  and queue_name = 'yinnidai_risk_credit_result'
order by _id desc;

sql解析：

message->>'$.pre_order_number':
message 是一列（通常是 JSON 格式数据）。
->> 是一种 JSON 操作符，允许提取 JSON 字段的文本值。

$.pre_order_number 是 JSONPath 表达式，用于定位 JSON 数据中的特定字段 pre_order_number。
所以 message->>'$.pre_order_number' 从 message 字段中提取 JSON 字段 pre_order_number 的值并将其作为结果输出。

from 子句
queue_consumer_exeception_record:
表名，表示从 queue_consumer_exeception_record 表中获取数据。

where 子句
用于过滤记录，包含以下条件：
create_date > '2024-05-07 00:00:00':
筛选出 create_date 大于指定日期的记录。

`status` = 0:
筛选出 status 列的值为 0 的记录。
注意：列名 status 被反引号包围，因为可能是 SQL 关键字。
queue_name = 'yinnidai_risk_credit_result':
筛选出 queue_name 列的值为 'yinnidai_risk_credit_result' 的记录。

order by 子句
order by _id desc:
按照 _id 列降序排序。

查询结果：

PO12345