我目前有一个看起来像这样的PostgreSQL表,数据列是JSON,我正在尝试根据可能位于JSON数据中也可能不在JSON数据中的键来查询该表。
| A | B | C | data |
----------------------------------------------------
| 1 | 2 | 3 | {} |
| 2 | 3 | 3 | {"name": "jack", "message": "123"} |
| 3 | 4 | 3 | {"name": "jill", "voice": "456"} |
| 4 | 2 | 3 | {"name": "bill", "email" "789"} |
目前我有
SELECT *
FROM (
SELECT
a,
b,
c,
coalesce(
CASE
WHEN (data ->> 'message') IS NULL
THEN NULL
ELSE (data ->> 'message')
END,
'') AS message,
coalesce(
CASE
WHEN (data ->> 'voice') IS NULL
THEN NULL
ELSE (data ->> 'voice')
END,
'') AS voice,
coalesce(
CASE
WHEN (data ->> 'email') IS NULL
THEN NULL
ELSE (data ->> 'email')
END,
'') AS email
FROM mytable) AS t
WHERE (t.message = "789" OR
t.voice = "789" OR
t.email = "789");
我当前收到错误,错误:"789"列不存在
我知道可能有一种更有效的方法。任何帮助非常感谢。
使用单引号t.message = '789'