Metabase中PostgreSQL查询JSONField指定键对应数组的元素个数问题

正确查询写法 #

1. 统计每行的my_list元素个数

SELECT json_array_length((data_field::json)->'my_list') AS my_list_count
FROM my_table;

2. 统计所有行的my_list元素总个数

SELECT SUM(json_array_length((data_field::json)->'my_list')) AS total_my_list_count
FROM my_table;

3. 兼容异常数据的写法（避免部分行没有my_list键、或my_list不是数组导致的报错）

SELECT json_array_length((data_field::json)->'my_list') AS my_list_count
FROM my_table
WHERE 
  (data_field::json)->'my_list' IS NOT NULL
  AND json_typeof((data_field::json)->'my_list') = 'array';

之前报错的原因 #

• 类型转换语法错误：PostgreSQL中类型转换使用双冒号::，你之前尝试用单冒号data_field:json不符合语法规范，直接触发语法报错。
• 运算优先级错误：你写的data_field->'my_list'::json会优先执行'my_list'::json将字符串转为JSON格式，再去取text类型的data_field的对应键，类型不匹配触发JSON解析错误。正确写法需要先把data_field转成JSON，用括号保证运算优先级：(data_field::json)->'my_list'
• 误用其他数据库语法：json_query_array是MySQL等其他数据库的JSON函数，PostgreSQL原生无该函数，因此调用失败。
• 多余操作：不需要调用json_object_keys遍历所有JSON键，直接用->操作符即可取出指定键的值，简化查询逻辑。

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