查询v_message视图JSON字段报错，求正确WHERE条件写法

我拥有一个名为v_message的视图，其定义如下：

SELECT 
message.id AS "id",
body AS "body",
message.created_at AS "createdAt",
from_phone_number AS "fromPhoneNumber",
to_phone_number AS "toPhoneNumber",
is_read AS "isRead",
json_build_object(
  'id', agent.id,
  'firstName', agent.first_name,
  'lastName', agent.last_name,
  'avatarLink', agent.avatar_link
) AS "createdByAgent",
json_build_object(
  'id', client.id,
  'firstName', client.first_name,
  'displayName', client.display_name
) AS "createdByClient"
FROM message 
LEFT JOIN agent
ON message.created_by_agent_id = agent.id
LEFt JOIN client
ON message.created_by_client_id = client.id

尝试执行以下带WHERE条件的查询：

SELECT * FROM v_message WHERE  v_message."createdByClient".id = 1

但出现错误：

ERROR:  missing FROM-clause entry for table "createdByClient"

请问该如何正确编写查询语句以实现筛选createdByClient的id为1的数据？

正确的查询写法 #

因为"createdByClient"是JSON类型的列，不是关联表，不能用表.列的方式访问其内部属性，需要使用PostgreSQL提供的JSON操作符或函数来提取JSON字段内的值：

方法1：使用->>操作符提取文本值并转为整数 #

SELECT * FROM v_message WHERE ("createdByClient"->>'id')::int = 1;

• ->>会将JSON属性值提取为文本类型，再通过::int转为整数，和数值1比较。

方法2：使用->操作符提取JSON值再转为整数 #

SELECT * FROM v_message WHERE ("createdByClient"->'id')::int = 1;

• ->提取的是JSON类型的值，直接转为整数后进行比较。

方法3：使用json_extract_path_text函数 #

SELECT * FROM v_message WHERE json_extract_path_text("createdByClient", 'id')::int = 1;

• 这个函数的作用是从JSON对象中提取指定路径的文本值，同样需要转为整数后比较。

补充说明 #

如果你的createdByClient字段可能为NULL（因为视图用了LEFT JOIN client），可以在条件里加上"createdByClient" IS NOT NULL避免空值报错：

SELECT * FROM v_message 
WHERE "createdByClient" IS NOT NULL 
AND ("createdByClient"->>'id')::int = 1;

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