确定每行JSON对象中哪些字段包含最多的空/空值的最佳方法是什么

Question

我有一个带有id列和data列的表。data列包含具有以下键的JSON对象：

• age
• name
• gender
• addresses(阵列(
• ratings
• specialties(阵列(

我想在每个JSON对象中找到最常为空或null的3个键。

我知道如何在Python中处理这一问题；我只需要迭代每一行，然后迭代该行的dataJSON对象中的每个值，并将结果存储在字典中。如果检测到一个空/空值，它将首先检查该键是否已经存在于结果字典中，如果是，则将该键的值加1。如果结果字典中还不存在该键，则会将其添加到字典中，起始值为1。从那里，我只需要对生成的字典进行排序，并取具有3个最高值的3个键。

为了清楚起见，这里有一个示例场景：

• 第1行：数据JSON对象中age和addresses键的值为空
• 第2行：数据JSON对象中age和specialties键的值为空
• 第3行：数据JSON对象中ratings和addresses键的值为空/null
• 第4行：数据JSON对象中name键的值为空/null
• 第5行：数据JSON对象中age键的值为空/null
• 第6行：数据JSON对象中age和addresses键的值为空/null
• 第7行：数据JSON对象中specialties键的值为空/null

在这个例子中，最常为空或为空的3个键是：

1. age(4行为空/空(
2. addresses(3行为空/空(
3. specialties(两行为空/空(

我将如何在Postgres中实现这一点？我想我必须制作一个自定义的循环函数，但我以前从未在Postgres中做过这样的事情，所以我非常感谢这里的指导。有什么解决这个问题的最佳方法的建议吗？

Answer 1

无需使用循环或自定义迭代。有了SQL的思维方式，从表中计数并按计数排序甚至比Python中更简单。

这里的秘密酱汁由jsonb_each函数和LATERAL子查询组成：

SELECT key, count(*)
FROM example t,
LATERAL jsonb_each(t.data)
WHERE value = 'null'
GROUP BY key
ORDER BY 2 DESC
LIMIT 3;

^{(在线演示(}

但是，请注意，通过对data进行迭代(就像在Python中一样(，您不会注意到JSON对象是否根本没有该属性——只有在存在的情况下才会进行迭代。data = '{}'根本不会被计算在内的列。如果你想把这些当作"；"空"；，实际上，您需要尝试使用任何现有密钥访问对象。这可以通过对已知密钥进行连接来实现：

SELECT key, count(*)
FROM example t,
UNNEST(ARRAY['age', 'name', 'gender', 'addresses', 'ratings', 'specialties']) AS keys(key)
WHERE data->key IS NULL OR data->key = 'null'
GROUP BY key
ORDER BY 2 DESC
LIMIT 3;

^{(在线演示(}