代码之家 › 专栏 › 技术社区 › ytu

过滤json值而不考虑PostgreSQL中的键

postgresql json sql

ytu · 技术社区 · 7 年前

diary 包括以下列:

| id | user_id | custom_foods       |
|----|---------|--------------------|
| 1  | 1       | {"56": 2, "42": 0} |
| 2  | 1       | {"19861": 1}       |
| 3  | 2       | {}                 |
| 4  | 3       | {"331": 0}         |

我想数一数有多少本日记 custom_foods

所需输出为:

| user_id | count   |
|---------|---------|
| 1       | 2       |
| 2       | 0       |
| 3       | 0       |

我从以下几点开始:

select *
from diary as d
join json_each_text(d.custom_foods) as e
on d.custom_foods != '{}'
where e.value > 0

错误:函数json\u each\u text(text)不存在

提示:没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换。

我使用的版本是:psql(10.5(ubuntu10.5-1.pgdg14.04+1),server9.4.19)。根据 PostgreSQL 9.4.19 Documentation ,该函数应该存在。我很困惑,现在不知道该怎么办。

我提到的线程:

1 回复 | 直到 5 年前

Kaushik Nayak 7 年前

你的 custom_foods json_each_text . 作为默认情况下,不考虑空json,您可以从单独的CTE获取空json的计数为0,并执行 UNION ALL

WITH empty AS
  ( SELECT DISTINCT user_id,
                    0 AS COUNT
   FROM diary
   WHERE custom_foods = '{}' )
SELECT user_id,
       count(CASE
                 WHEN VALUE::int > 0 THEN 1
             END)
FROM diary d,
     json_each_text(d.custom_foods::JSON)
GROUP BY user_id
 UNION ALL
   SELECT *
FROM empty
ORDER BY user_id;

Demo