PostgreSQL 11如何通过另一表存储的字符串路径查询JSONB字段值

这个问题我之前也帮人处理过，核心就是把逗号分隔的路径字符串转换成PostgreSQL能识别的JSONB路径格式，这里有两种实用的方案，直接就能落地：

方法一：用string_to_array + jsonb_extract_path_text + VARIADIC关键字 #

PostgreSQL的jsonb_extract_path_text函数支持传入多个路径分量作为参数，而VARIADIC关键字可以把数组拆成单独的参数传给函数，完美匹配我们的需求。

假设你有两张表：

• main_table：包含目标JSONB字段data和关联ID
• path_table：存储逗号分隔的路径字符串json_path（比如'key1,subKey'）和对应的关联ID

可以用下面的SQL查询：

SELECT
  mt.id,
  jsonb_extract_path_text(mt.data, VARIADIC string_to_array(pt.json_path, ',')) AS extracted_value
FROM
  main_table mt
JOIN
  path_table pt ON mt.assoc_id = pt.assoc_id;

原理说明： #

1. string_to_array(pt.json_path, ',')把逗号分隔的字符串转成文本数组，比如'key1,subKey'变成['key1', 'subKey']
2. VARIADIC关键字告诉PostgreSQL把数组的每个元素作为独立参数传给jsonb_extract_path_text，等价于手动写jsonb_extract_path_text(mt.data, 'key1', 'subKey')
3. 最终效果和你直接用data#>'{key1,subKey}'完全一致，路径不存在时会返回null

方法二：转换成JSONPath表达式查询 #

如果你的路径可能更复杂（比如包含数组下标），可以把逗号分隔的字符串转成JSONPath格式，用jsonb_path_query_first函数查询：

SELECT
  mt.id,
  jsonb_path_query_first(mt.data, format('$.%s', replace(pt.json_path, ',', '.'))) AS extracted_value
FROM
  main_table mt
JOIN
  path_table pt ON mt.assoc_id = pt.assoc_id;

原理说明： #

1. replace(pt.json_path, ',', '.')把逗号换成点，'key1,subKey'变成'key1.subKey'
2. format('$.%s', ...)拼接成标准的JSONPath表达式'$.key1.subKey'
3. jsonb_path_query_first会返回路径匹配的第一个值，同样支持数组路径（比如'key1,0,subKey'会转成'$.key1[0].subKey'，直接就能用）

注意事项 #

• 如果你的路径分量本身包含逗号，那得换个分隔符（比如竖线|），否则会拆分错误
• 两种方法在路径不存在时都会返回null，和原生#>操作符的行为一致

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