Cast top-level JSONB field as UUID-CodePudding

Consider a table like so:

CREATE TABLE items (
  e uuid,
  v jsonb
)

I insert the following values:

INSERT INTO items (e, v) VALUES
  ('9a70439e-33c0-4b34-91f5-efac20b58301', '"92cb730c-8b4f-46ef-9925-4fab953694c6"'),
  ('92cb730c-8b4f-46ef-9925-4fab953694c6', '"Bob"'),
  ('92cb730c-8b4f-46ef-9925-4fab953694c6', '52');

Note how for v, I have actually stringified text and numbers -- not an object.

Now, what if I wanted to write a query like so:

WITH match AS (
  SELECT * FROM items WHERE e = '9a70439e-33c0-4b34-91f5-efac20b58301'
) SELECT * FROM items JOIN match ON match.v = items.e;

I would get:

Query Error: error: operator does not exist: jsonb = uuid

Is there a way I could tell postgres to "parse" the jsonb value, and see if it is a uuid?

I know about Postgres cast to UUID from JSON , where the ->> operator was able to do the trick. But I can't do that in this case, because our json value is a strong not an object.

CodePudding user response：

You can use ->> 0 to extract the value as text:

SELECT * FROM items
WHERE e = '9a70439e-33c0-4b34-91f5-efac20b58301'
  AND e::text = v ->> 0;