我有一个正在查询的DB表,简化示例如下:
+----------+---------+----------------------------------------------------------------------------------------------------------------+
| entry_id | list_id | availability |
|----------+---------+----------------------------------------------------------------------------------------------------------------|
| 130 | 199 | {"2021-10-20": {"value": "no_reply", "notify": false}, "2021-10-21": {"value": "no_reply", "notify": false}} |
| 131 | 199 | {"2021-10-20": {"value": "no_reply", "notify": false}, "2021-10-21": {"value": "no_reply", "notify": false}} |
| 132 | 199 | {} |
| 129 | 199 | {"2021-10-20": {"value": "available", "notify": false}, "2021-10-21": {"value": "available", "notify": false}} |
+----------+---------+----------------------------------------------------------------------------------------------------------------+
在我的查询中,我只想包括可用性列dict具有关键字的行,并过滤掉任何空的行(例如entry_id 132(,默认值为"0";{}";
我在查询中使用ListEntry.availability != '"{}"'
作为过滤器取得了一些成功,但这似乎有点棘手,我想知道是否有更好的方法?
您无法将比较的右侧转换为jsonb:
SELECT * FROM ListEntry WHERE availability <> '{}'::jsonb