jsonb support for <@, ?| and ?& added.pr/101

need to see if equality already works.

2 files changed, 37 insertions, 1 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py
index 183cb2695..d19dbe118 100644
--- a/lib/sqlalchemy/dialects/postgresql/json.py
+++ b/lib/sqlalchemy/dialects/postgresql/json.py
@@ -279,8 +279,9 @@ class JSONB(JSON):
             return JSONElement(self.expr, other)
 
         def _adapt_expression(self, op, other_comparator):
+            # How does one do equality?? jsonb also has "=" eg. '[1,2,3]'::jsonb = '[1,2,3]'::jsonb
             if isinstance(op, custom_op):
-                if op.opstring in ['?', '@>']:
+                if op.opstring in ['?', '?&', '?|', '@>', '<@']:
                     return op, sqltypes.Boolean
                 if op.opstring == '->':
                     return op, sqltypes.Text
@@ -293,10 +294,26 @@ class JSONB(JSON):
             """
             return self.expr.op('?')(other)
 
+        def has_all(self, other):
+            """Boolean expression.  Test for presence of all keys in jsonb
+            """
+            return self.expr.op('?&')(other)
+
+        def has_any(self, other):
+            """Boolean expression.  Test for presence of any key in jsonb
+            """
+            return self.expr.op('?|')(other)
+
         def contains(self, other, **kwargs):
             """Boolean expression.  Test if keys (or array) are a superset of/contained
             the keys of the argument jsonb expression.
             """
             return self.expr.op('@>')(other)
 
+        def contained_by(self, other):
+            """Boolean expression.  Test if keys are a proper subset of the
+            keys of the argument jsonb expression.
+            """
+            return self.expr.op('<@')(other)
+
 ischema_names['jsonb'] = JSONB
\ No newline at end of file
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index d4d7d3766..87250d467 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -2009,12 +2009,31 @@ class JSONBTest(JSONTest):
             "test_table.test_column ? %(test_column_1)s"
         )
 
+    def test_where_has_all(self):
+        self._test_where(
+            self.jsoncol.has_all({'name': 'r1', 'data': {"k1": "r1v1", "k2": "r1v2"}}),
+            "test_table.test_column ?& %(test_column_1)s"
+        )
+
+    def test_where_has_any(self):
+        self._test_where(
+            self.jsoncol.has_any(postgresql.array(['name', 'data'])),
+            "test_table.test_column ?| ARRAY[%(param_1)s, %(param_2)s]"
+        )
+
     def test_where_contains(self):
         self._test_where(
             self.jsoncol.contains({"k1": "r1v1"}),
             "test_table.test_column @> %(test_column_1)s"
         )
 
+    def test_where_contained_by(self):
+        self._test_where(
+            self.jsoncol.contained_by({'foo': '1', 'bar': None}),
+            "test_table.test_column <@ %(test_column_1)s"
+        )
+
+
 class JSONBRoundTripTest(JSONRoundTripTest):
     __only_on__ = ('postgresql >= 9.4',)