如何从包含JSON数组的列中提取transaction_id？

以下是主流数据库中，从存储JSON数组的列里提取transaction_id字段（字段不存在时返回NULL或空值）的实现方法：

MySQL #

假设存储JSON数组的列名为json_data，表名为your_table：

• 提取数组中所有对象的transaction_id，返回多行结果：

SELECT JSON_EXTRACT(json_data, CONCAT('$[', idx, '].transaction_id')) AS transaction_id
FROM your_table
JOIN (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS indexes
WHERE idx < JSON_LENGTH(json_data);

• 只提取数组中第一个对象的transaction_id：

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_data, '$[0].transaction_id')) AS transaction_id
FROM your_table;

JSON_UNQUOTE用于去掉结果的引号，直接返回字段原始值。

PostgreSQL #

PostgreSQL原生支持json/jsonb类型，假设列名为json_data，表名为your_table：

• 提取数组中所有对象的transaction_id，返回多行结果：

SELECT json_array_elements(json_data)->>'transaction_id' AS transaction_id
FROM your_table;

• 提取第一个对象的transaction_id：

SELECT json_data->0->>'transaction_id' AS transaction_id
FROM your_table;

->>用于直接返回文本类型的字段值，无需额外处理引号。

SQL Server #

假设列名为json_data，表名为your_table：

• 提取数组中所有对象的transaction_id：

SELECT JSON_VALUE(j.value, '$.transaction_id') AS transaction_id
FROM your_table
CROSS APPLY OPENJSON(json_data) AS j;

• 提取第一个对象的transaction_id：

SELECT JSON_VALUE(json_data, '$[0].transaction_id') AS transaction_id
FROM your_table;

补充说明 #

• 以上方法都会在transaction_id字段不存在时返回NULL，无需额外判断；
• 如果你的列存储的是单个JSON对象而非数组，只需去掉数组索引部分（比如用$.transaction_id替代$[0].transaction_id）即可。

内容的提问来源于stack exchange，提问作者DKCroat