PostgreSQL自定义函数调用jsonb_array_elements时出现语法错误求助

函数报错原因 #

你写的PL/pgSQL函数报错，是因为PL/pgSQL的FOR...IN循环必须搭配SELECT查询语句，不能直接调用集合返回函数jsonb_array_elements，需要把该函数放到SELECT中才能遍历结果。另外，最好显式声明循环变量的类型，避免隐式类型转换问题。

修正后的PL/pgSQL函数 #

CREATE OR REPLACE FUNCTION get_object_keys(jsonb_array jsonb)
RETURNS text[] AS $$
DECLARE
  object_keys text[];
  item jsonb; -- 显式声明变量类型
BEGIN
  -- 将jsonb_array_elements放入SELECT语句中
  FOR item IN SELECT jsonb_array_elements(jsonb_array) LOOP
    object_keys := array_append(object_keys, item->>'object_key');
  END LOOP;
  RETURN object_keys;
END;
$$ LANGUAGE plpgsql;

更适合JOIN/WHERE子句的实现方式 #

其实不需要自定义函数，直接用PostgreSQL内置的JSON函数就能高效实现需求，而且在JOIN/WHERE中更灵活：

1. 直接提取object_key为数组 #

用jsonb_array_elements展开数组，再用array_agg聚合为数组：

SELECT array_agg(obj->>'object_key') AS object_keys
FROM jsonb_array_elements('[{"name": "ARM", "object_key": "GOB-1211"}, {"name": "foo", "object_key": "GOB-1234"}]'::jsonb) AS obj;

如果是针对表中的jsonb字段（比如表test_table的json_data字段）：

SELECT array_agg(obj->>'object_key') AS object_keys
FROM test_table, jsonb_array_elements(test_table.json_data) AS obj;

2. 在WHERE子句中筛选包含指定object_key的行 #

比如要找出json_data字段中包含GOB-1211的行：

SELECT *
FROM test_table
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(test_table.json_data) AS obj
  WHERE obj->>'object_key' = 'GOB-1211'
);

3. 更简洁的路径查询方式 #

用jsonb_path_query_array直接通过JSON路径提取数组，再转为text数组：

SELECT jsonb_path_query_array(
  '[{"name": "ARM", "object_key": "GOB-1211"}, {"name": "foo", "object_key": "GOB-1234"}]'::jsonb,
  '$[*].object_key'
)::text[] AS object_keys;

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