Postgres Insert based on JSON data

Question

Trying to do an insert based on whether a value all ready exists or not in a JSON blob in a Postgres table.

| a | b | c | d |       metadata                    |
_____________________________________________________
| 1 | 2 | 3 | 4 | {"other-key": 1}                  |
| 2 | 1 | 4 | 4 | {"key": 99}                       |
| 3 | 1 | 4 | 4 | {"key": 99, "other-key": 33}      |

Currently trying to use something like this.

INSERT INTO mytable (a, b, c, d, metadata)
SELECT :a, :b, :c, :d, :metadata::JSONB
WHERE
  (:metadata->>'key'::TEXT IS NULL
   OR :metadata->>'key'::TEXT NOT IN (SELECT :metadata->>'key'::TEXT
                                   FROM mytable));

But keep getting an ERROR: operator does not exist: unknown ->> boolean Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

sir_t sir_t · Accepted Answer · 2017-11-16T12:21:05

It was just a simple casting issue

INSERT INTO mytable (a, b, c, d, metadata)
SELECT :a, :b, :c, :d, :metadata::JSONB
WHERE
  ((:metadata::JSONB->>'key') is NULL or
  :metadata::JSONB->>'key' NOT IN (
                                  SELECT  metadata->>'key' FROM mytable
                                  WHERE 
                                  metadata->>'key' =  :metadata::JSONB->>'key'));

Postgres Insert based on JSON data

1 Answers