PostgreSQL中JSON类型存储数组时操作报错的类型判断问题

受Stack Overflow平台相关问题启发（需求为修改JSON字段中priceRange的取值），搭建如下测试表与测试数据：

create table house(
    sale json
);

insert into house (sale) values ('{"houses":[{"houseId":"house100","houseLocation": "malvern","attribute":{"colour":["white","grey"],"openForInspection":{"fromTime": "0001","toTime": "2359"}},"priceRange":null}]}')

执行查询提取houses字段值并查看类型：

select
    sale,
    sale->'houses',
    pg_typeof(sale->'houses')
from house

查询结果显示pg_typeof(sale->'houses')返回值为json类型。

随后尝试调用json_object_keys处理该值：

select
    sale,
    sale->'houses',
    pg_typeof(sale->'houses'),
    json_object_keys(sale->'houses')
from house

执行报错：error: cannot call json_object_keys on an array，与此前pg_typeof返回的json类型结论看似矛盾。

接着尝试通过数组下标访问该值的第一个元素：

select
    sale,
    sale->'houses',
    pg_typeof(sale->'houses'),
    (sale->'houses')[0]
from house

再次报错：error: cannot subscript type json because it is not an array，又和上一个报错提到的「值为数组」的提示看似矛盾。

核心问题是混淆了两个完全不同维度的「类型」定义：

• PostgreSQL SQL层面的数据类型：pg_typeof返回的是SQL类型系统的类型标记，所有通过->操作符从json类型字段中提取的值，无论内部JSON结构是对象、数组、字符串、数值还是null，在SQL层面统一归为json类型，该标记不会随JSON内部结构变化而改变。
• JSON值的内部结构类型：两次报错中提到的「array」分属不同语境，和SQL层面的json类型标记没有冲突：
  • 第一次报错的「array」指JSON规范定义的数组结构：json_object_keys函数仅支持接收JSON对象结构的入参，作用是返回对象的所有顶层键名；提取的sale->'houses'是JSON数组结构，传入后自然触发报错。
  • 第二次报错的「array」指PostgreSQL原生SQL数组类型（例如text[]、int[]这类SQL数组才支持下标语法访问）：PostgreSQL 14以前的版本不支持使用SQL原生数组下标[n]访问json类型内的JSON数组元素，对SQL类型系统来说json本身不是SQL数组类型，因此触发报错。

• 访问JSON数组内的指定位置元素，使用->搭配索引即可（JSON数组索引从0开始计数）：

select
    sale,
    sale->'houses',
    pg_typeof(sale->'houses'),
    sale->'houses'->0 as first_house
from house

• 遍历JSON数组的所有元素，使用专门处理JSON数组的json_array_elements函数，不要误用处理JSON对象的json_object_keys：

select
    sale,
    json_array_elements(sale->'houses') as single_house
from house

提示：PostgreSQL 14及以上版本才支持直接用[n]下标语法访问json/jsonb类型的内部元素，低版本必须使用-> 索引的写法。

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