Postgres query json data

Question

I currently have a PostgreSQL table that looks something like this, the data column is JSON, I am trying to query the table based on keys that may or may not be in the JSON data.

 | 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"} |

Currently I have

 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");

I am currently getting the error, ERROR: column "789" does not exist

I know there is probably a more efficient way of doing it. Any help greatly appreciated.

• user330315: Strings need to be enclosed in single quotes in SQL. Double quotes are for identifiers.
• sir_t: Ahhhhhh! so obvious. Thanks

Answer 1

use single quotes t.message = '789'