如何从JSON数组中按指定id提取对应值插入SQL，不依赖数组索引

你要的匹配id取值不需要依赖数组下标，绝大多数支持JSON操作的主流数据库都支持按条件过滤JSON数组的写法，核心思路是遍历JSON数组匹配id字段后提取对应value。

通用JSON路径过滤写法 #

对于支持标准JSONPath语法的数据库，直接使用条件过滤表达式即可，路径写法为：
$.Custom[?(@.id == 200)].value

这个表达式会自动遍历Custom数组，匹配所有id等于200的元素，返回对应的value值，完全不依赖数组元素顺序。

各主流数据库实现示例 #

MySQL 8.0+ #

使用JSON_EXTRACT搭配过滤表达式即可，若要返回可直接插入的数值类型，写法如下：

SELECT CAST(
  JSON_UNQUOTE(JSON_EXTRACT(your_json_column, '$.Custom[?(@.id == 200)].value')) 
  AS UNSIGNED
) AS custom_entry
FROM your_table;

PostgreSQL #

使用jsonb_array_elements（json类型对应json_array_elements）拆分数组后过滤：

SELECT (array_elem->>'value')::INT AS custom_entry
FROM your_table,
jsonb_array_elements(your_json_column -> 'Custom') array_elem
WHERE array_elem ->> 'id' = '200';

SQL Server #

使用OPENJSON解析数组后过滤：

SELECT custom_value AS custom_entry
FROM your_table
CROSS APPLY OPENJSON(your_json_column, '$.Custom')
WITH (
  custom_id INT '$.id',
  custom_value INT '$.value'
)
WHERE custom_id = 200;

注意：

1. JSON路径的字段名大小写需要和实际JSON结构完全匹配，如果你实际存储的是小写custom就把路径里的Custom换成小写即可。
2. 你之前的下标写法本身存在错误，JSON数组下标从0开始计数，你示例中id=200是数组的第二个元素，对应下标为[1]而非[2]，即使不调整顺序原来的写法也会取到空值或错误值。

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