CODEWITHSUNDEEP
sqljsonpostgresql
sir_t
sir_t

Reputation: 133

Postgres query json data

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.

Upvotes: 0

Views: 53

Answers (1)

classicalConditioning
classicalConditioning

Reputation: 655

use single quotes t.message = '789'

Upvotes: 1

Related Questions