PostgreSQL 如何对 JSON 类型的列进行分组 / 选择 (PG::UndefinedFunction: ERROR: could not identify an equality operator for type json)

在本文中，我们将介绍如何在 PostgreSQL 中对 JSON 类型的列进行分组和选择。当我们尝试对 JSON 类型的列进行操作时，可能会遇到 “PG::UndefinedFunction: ERROR: could not identify an equality operator for type json” 的错误。这是因为 PostgreSQL 默认情况下不提供针对 JSON 类型的等值运算符。然而，我们可以使用一些技巧来解决这个问题。

阅读更多：PostgreSQL 教程

创建示例表格

首先，让我们创建一个示例表格来说明问题。假设我们有一个名为 “employees” 的表格，其中包含员工的姓名和各自的技能等级。技能等级被存储为 JSON 类型的列。

CREATE TABLE employees (
  name VARCHAR(100),
  skills JSON
);

INSERT INTO employees (name, skills)
VALUES 
  ('John', '{"skill1": "expert", "skill2": "advanced"}'),
  ('Alice', '{"skill1": "advanced", "skill2": "intermediate"}'),
  ('Bob', '{"skill1": "intermediate", "skill2": "beginner"}');

使用箭头操作符 (->) 进行选择

要选择特定的 JSON 属性，我们可以使用箭头操作符 (->)。例如，要选择所有技能等级为 “advanced” 的员工，可以使用以下查询：

SELECT * FROM employees
WHERE skills -> 'skill1' = '"advanced"';

使用箭头操作符 (->>) 进行选择

如果我们想要选择 JSON 属性的值，并将其与其他值进行比较，可以使用箭头操作符 (->>)。例如，要选择技能等级大于 “intermediate” 的员工，可以使用以下查询：

SELECT * FROM employees
WHERE skills ->> 'skill1' > 'intermediate';

使用 jsonb 类型和等值运算符

另一种解决方法是使用 jsonb 类型，它提供了用于 json 类型的等值运算符。我们可以在创建表格时将列的类型更改为 jsonb，并使用等值运算符执行分组和选择操作。以下是使用 jsonb 类型和等值运算符进行技能等级分组的示例：

CREATE TABLE employees (
  name VARCHAR(100),
  skills jsonb
);

INSERT INTO employees (name, skills)
VALUES 
  ('John', '{"skill1": "expert", "skill2": "advanced"}'),
  ('Alice', '{"skill1": "advanced", "skill2": "intermediate"}'),
  ('Bob', '{"skill1": "intermediate", "skill2": "beginner"}');

SELECT skills ->> 'skill1' AS skill, COUNT(*) AS count
FROM employees
GROUP BY skills ->> 'skill1';

以上查询将根据技能等级对员工进行分组，并计算每个技能等级的数量。

自定义函数解决问题

如果不想改变列的类型或无法使用等值运算符，还可以创建自定义函数来解决 “could not identify an equality operator for type json” 的错误。例如，我们可以创建一个函数来比较 JSON 值：

CREATE OR REPLACE FUNCTION json_equals(json1 json, json2 json)
RETURNS boolean AS BEGIN
  RETURN json1 = json2;
END; LANGUAGE plpgsql;

然后，我们可以在查询中调用此函数进行 JSON 值的比较：

SELECT * FROM employees
WHERE json_equals(skills, '{"skill1": "advanced", "skill2": "intermediate"}');

使用自定义函数，我们可以绕过 PostgreSQL 默认的限制，对 JSON 类型的列进行等值比较。

总结

在本文中，我们介绍了如何在 PostgreSQL 中对 JSON 类型的列进行分组和选择。当遇到 “PG::UndefinedFunction: ERROR: could not identify an equality operator for type json” 的错误时，我们可以使用箭头操作符 (-> 和 ->>) 或者将列的类型更改为 jsonb 来解决问题。此外，我们还可以创建自定义函数来执行 JSON 值的比较。通过这些技巧，我们可以更灵活地处理 JSON 数据，并进行相关的查询和分析操作。