Aurora PostgreSQL查询嵌套JSONB记录中独立字段值的解决方案

需求1：permissions列返回无多余双引号的列表格式 #

你表中存储的permissions为键是字符串数字的JSON对象，我们可以提取所有权限值转为PostgreSQL原生文本数组，输出时不会带有JSON序列化的多余双引号，还支持按权限键的数字顺序排序：

SELECT 
  json_line_item ->> 'type' AS "type", 
  json_line_item ->> 'status' AS "status",
  -- 提取所有权限值转为有序文本数组，无多余引号
  ARRAY(
    SELECT json_line_item -> 'permissions' ->> key
    FROM jsonb_object_keys(json_line_item -> 'permissions') key
    ORDER BY key::integer
  ) AS "permissions"
FROM newtable,
LATERAL jsonb_array_elements(newtable.column1 -> 'usertype') json_line_item;

如果需要逗号分隔的纯文本列表，只需将数组聚合改为字符串聚合即可：

SELECT 
  json_line_item ->> 'type' AS "type", 
  json_line_item ->> 'status' AS "status",
  STRING_AGG(json_line_item -> 'permissions' ->> key, ',' ORDER BY key::integer) AS "permissions"
FROM newtable,
LATERAL jsonb_array_elements(newtable.column1 -> 'usertype') json_line_item,
LATERAL jsonb_object_keys(json_line_item -> 'permissions') key
GROUP BY 1, 2;

需求2：按索引获取单个权限值 #

注意你存储的permissions的键为字符串类型的"1"、"2"，不是数组的整数下标，取值时将索引转为字符串作为键查询即可，示例如下：

SELECT 
  json_line_item ->> 'type' AS "type", 
  json_line_item ->> 'status' AS "status",
  -- 获取索引为1的权限值，输出无多余引号
  json_line_item -> 'permissions' ->> '1' AS "permissions[1]",
  -- 获取索引为2的权限值，输出无多余引号
  json_line_item -> 'permissions' ->> '2' AS "permissions[2]"
FROM newtable,
LATERAL jsonb_array_elements(newtable.column1 -> 'usertype') json_line_item;

如果需要动态传入整数参数查询，只需将整数参数强转为文本即可，比如参数为n时写法为：json_line_item -> 'permissions' ->> n::text，不存在的索引会返回NULL不会报错。

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