Postgres查询JSON数据
问题描述:
我目前有一个看起来像这样的PostgreSQL表,数据列是JSON,我试图根据可能或不可能在JSON数据中的键来查询表。Postgres查询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'
字符串需要用SQL中的单引号引起来。双引号是标识符。 –
Ahhhhhh!如此明显。谢谢 –