jsonb #>> operator is not supported

[andymitchell]

Describe the bug

operator does not exist: jsonb #>> text

🐜 This seems to be an execution error, which means that your request syntax seems okay, but the resulting statement cannot be executed → Probably not a pg-mem error.

*️⃣ Failed SQL statement: SELECT "obj" FROM test_table WHERE obj#>>'{id}' = 'first';

It also failed on variants: SELECT "obj" FROM test_table WHERE (obj#>>'{id}')::text = 'first'; SELECT "obj" FROM test_table WHERE obj::jsonb#>>'{id}' = 'first'; SELECT "obj" FROM "test_table" WHERE (obj#>>'{age}')::int = 1;

To Reproduce

CREATE TABLE IF NOT EXISTS test_table ( pk SERIAL PRIMARY KEY, obj JSONB NOT NULL, unique_key VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW());

CREATE INDEX IF NOT EXISTS idx_unique_key ON test_table (unique_key);

DELETE FROM test_table; INSERT INTO test_table (obj, unique_key) VALUES ('{"id": "first", "name": "Bob", "age": 6}', 'first');

SELECT "obj" FROM "test_table" WHERE (obj#>>'{age}')::int = 1;

pg-mem version

2.8.1

Functional equivalence

#>> is a simpler nesting syntax for "->>". E.g. {"children": {"Bob": {"age": 1}}} #>>{children,Bob,age} vs ->children->Bob->>age

pg-mem does correctly support the other syntax (but I'm using a library that is giving me SQL in the form of #>>).