如何在PostgreSQL中实现MariaDB的JSON_LENGTH函数功能？

PostgreSQL没有直接对应MariaDB JSON_LENGTH的内置函数，但可以通过自定义PL/pgSQL函数实现完全一致的行为，解决你遇到的类型兼容和集合函数报错问题。

自定义函数实现 #

CREATE OR REPLACE FUNCTION json_length(json_val json)
RETURNS integer AS $$
BEGIN
    CASE json_typeof(json_val)
        WHEN 'array' THEN
            RETURN json_array_length(json_val);
        WHEN 'object' THEN
            RETURN (SELECT count(*) FROM json_object_keys(json_val));
        WHEN 'string', 'number', 'boolean', 'null' THEN
            RETURN 1;
        ELSE
            RETURN NULL;
    END CASE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

函数逻辑说明 #

• 用json_typeof判断输入JSON的类型，分场景处理：
  • 数组类型：直接调用json_array_length返回根层级元素数量
  • 对象类型：通过子查询统计json_object_keys返回的键的数量（避免集合函数直接在CASE分支中返回导致的报错）
  • 标量类型（字符串、数字、布尔、null）：返回1，匹配MariaDB对单个标量值的计数规则
  • 未知类型：返回null

验证示例 #

执行以下查询，结果与你提供的MariaDB示例完全一致：

SELECT
    json_length('{"test": 123}'::json) as test1, -- 1
    json_length('"123"'::json) as test2, -- 1
    json_length('123'::json) as test3, -- 1 
    json_length('[]'::json) as test4, -- 0
    json_length('[123]'::json) as test5, -- 1
    json_length('[123, 456]'::json) as test6, -- 2
    json_length('[123, {"test": 123}]'::json) as test7; -- 2

解决你之前遇到的问题 #

1. CASE语句报错：原问题是json_object_keys属于返回行的集合函数，不能直接在CASE分支中返回，改用子查询count(*)将其转换为单个数值即可解决。
2. IF ELSE语法错误：PostgreSQL的SQL语句中不支持MySQL/MariaDB风格的IF ELSE，应使用CASE WHEN；而在PL/pgSQL函数中可以使用IF，但上述自定义函数用CASE更简洁统一。

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