PostgreSQL中如何为JSON类型添加有效过滤条件？

我有一段可正常执行的PostgreSQL查询语句：

SELECT  tableA.id
       ,json_elements.each_section -> 'id'    AS parameter_name
       ,json_elements.each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN
(
    SELECT  tableA.id                                          AS id
           ,JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    FROM tableA
) json_elements
ON tableA.id = json_elements.id

但添加过滤条件后执行报错：

SELECT  tableA.id
       ,json_elements.each_section -> 'id'    AS parameter_name
       ,json_elements.each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN
(
    SELECT  tableA.id                                          AS id
           ,JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    FROM tableA
) json_elements
ON tableA.id = json_elements.id
WHERE (json_elements.each_section -> 'id') = 'exam'

报错信息：

SQL Error [42883]: ERROR: operator does not exist: json = unknown
No operator matches the given name and argument types. You might need to add explicit type casts.

尝试转成JSON值后仍然报错：

SELECT  tableA.id
       ,json_elements.each_section -> 'id'    AS parameter_name
       ,json_elements.each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN
(
    SELECT  tableA.id                                          AS id
           ,JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    FROM tableA
) json_elements
ON tableA.id = json_elements.id
WHERE (json_elements.each_section -> 'id') = to_json('exam'::text)

报错信息：

SQL Error [42883]: ERROR: operator does not exist: json = json
No operator matches the given name and argument types. You might need to add explicit type casts.

PostgreSQL中，->操作符返回的是JSON类型，而原生JSON类型不支持直接用=做等值比较，需要用以下方式实现过滤：

方式1：使用->>获取文本值后比较 #

->>操作符会直接提取JSON字段对应的文本类型，可直接与字符串比较：

SELECT  tableA.id
       ,json_elements.each_section -> 'id'    AS parameter_name
       ,json_elements.each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN
(
    SELECT  tableA.id                                          AS id
           ,JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    FROM tableA
) json_elements
ON tableA.id = json_elements.id
WHERE json_elements.each_section ->> 'id' = 'exam'

方式2：将JSON值转为文本类型后比较 #

若坚持用->，可把返回的JSON值转为text类型，注意JSON字符串转文本后会保留双引号：

SELECT  tableA.id
       ,json_elements.each_section -> 'id'    AS parameter_name
       ,json_elements.each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN
(
    SELECT  tableA.id                                          AS id
           ,JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    FROM tableA
) json_elements
ON tableA.id = json_elements.id
WHERE (json_elements.each_section -> 'id')::text = '"exam"'

方式3：提前在子查询中过滤（更高效） #

把过滤条件放到子查询里，减少关联的数据量，提升性能：

SELECT  tableA.id
       ,json_elements.each_section -> 'id'    AS parameter_name
       ,json_elements.each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN
(
    SELECT  tableA.id                                          AS id
           ,JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    FROM tableA
    WHERE JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) ->> 'id' = 'exam'
) json_elements
ON tableA.id = json_elements.id

也可以用LATERAL JOIN简化写法：

SELECT  tableA.id
       ,each_section -> 'id'    AS parameter_name
       ,each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN LATERAL JSON_ARRAY_ELEMENTS(tableA.operation_values::JSON) AS each_section
    ON each_section ->> 'id' = 'exam'

方式4：使用jsonb类型（推荐） #

如果字段是jsonb类型（比JSON类型更灵活，支持更多操作），可直接用@>判断包含关系：

SELECT  tableA.id
       ,each_section -> 'id'    AS parameter_name
       ,each_section -> 'value' AS parameter_value
FROM tableA
LEFT JOIN LATERAL JSONB_ARRAY_ELEMENTS(tableA.operation_values::JSONB) AS each_section
    ON each_section @> '{"id": "exam"}'::jsonb

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