Repository: kayak/pypika
Pull request #311 (2019-08-29 09:53:23)

Reopening reverted PR to refactor JSON operators
Accidentally merged https://github.com/kayak/pypika/pull/306 so I reverted it. Reopening this same PR for review.


Patch:
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 0f9545a..65e1d15 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -66,6 +66,7 @@ from pypika.terms import (
     EmptyCriterion,
     Field,
     Interval,
+    JSON,
     Not,
     NullValue,
     Parameter,
diff --git a/pypika/enums.py b/pypika/enums.py
index 903cc6a..e775f4f 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -128,7 +128,7 @@ class Dialects(Enum):
     SNOWFLAKE = 'snowflake'
 
 
-class PostgresOperators(Enum):
+class JSONOperators(Enum):
     HAS_KEY = '?'
     CONTAINS = '@>'
     CONTAINED_BY = '<@'
@@ -136,4 +136,6 @@ class PostgresOperators(Enum):
     HAS_ANY_KEYS = '?|'
     GET_JSON_VALUE = '->'
     GET_TEXT_VALUE = '->>'
+    GET_PATH_JSON_VALUE = '#>'
+    GET_PATH_TEXT_VALUE = '#>>'
 
diff --git a/pypika/queries.py b/pypika/queries.py
index 2128008..451375c 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -50,6 +50,10 @@ class Selectable:
     def __getattr__(self, name):
         return self.field(name)
 
+    @ignore_copy
+    def __getitem__(self, name):
+        return self.field(name)
+
 
 class AliasedQuery(Selectable):
     def __init__(self, name, query=None):
@@ -393,7 +397,7 @@ class _UnionQuery(Selectable, Term):
         clauses = []
         selected_aliases = {s.alias for s in self.base_query._selects}
         for field, directionality in self._orderbys:
-            term = "{quote}{alias}{quote}".format(alias=field.alias, quote=quote_char or '') \
+            term = format_quotes(field.alias, quote_char) \
                 if field.alias and field.alias in selected_aliases \
                 else field.get_sql(quote_char=quote_char, **kwargs)
 
@@ -415,7 +419,12 @@ class QueryBuilder(Selectable, Term):
     state to be branched immutably.
     """
 
-    def __init__(self, quote_char='"', dialect=None, wrap_union_queries=True, wrapper_cls=ValueWrapper):
+    def __init__(self,
+                 quote_char='"',
+                 secondary_quote_char="'",
+                 dialect=None,
+                 wrap_union_queries=True,
+                 wrapper_cls=ValueWrapper):
         super(QueryBuilder, self).__init__(None)
 
         self._from = []
@@ -454,6 +463,7 @@ class QueryBuilder(Selectable, Term):
         self._foreign_table = False
 
         self.quote_char = quote_char
+        self.secondary_quote_char = secondary_quote_char
         self.dialect = dialect
         self.wrap_union_queries = wrap_union_queries
 
@@ -866,6 +876,7 @@ class QueryBuilder(Selectable, Term):
 
     def get_sql(self, with_alias=False, subquery=False, **kwargs):
         kwargs.setdefault('quote_char', self.quote_char)
+        kwargs.setdefault('secondary_quote_char', self.secondary_quote_char)
         kwargs.setdefault('dialect', self.dialect)
 
         if not (self._selects or self._insert_table or self._delete_from or self._update_table):
@@ -1058,10 +1069,7 @@ class QueryBuilder(Selectable, Term):
         selected_aliases = {s.alias for s in self._selects}
         for field in self._groupbys:
             if groupby_alias and field.alias and field.alias in selected_aliases:
-                clauses.append("{quote}{alias}{quote}".format(
-                      alias=field.alias,
-                      quote=quote_char or '',
-                ))
+                clauses.append(format_quotes(field.alias, quote_char))
             else:
                 clauses.append(field.get_sql(quote_char=quote_char, **kwargs))
 
@@ -1084,7 +1092,7 @@ class QueryBuilder(Selectable, Term):
         clauses = []
         selected_aliases = {s.alias for s in self._selects}
         for field, directionality in self._orderbys:
-            term = "{quote}{alias}{quote}".format(alias=field.alias, quote=quote_char or '') \
+            term = format_quotes(field.alias, quote_char) \
                 if orderby_alias and field.alias and field.alias in selected_aliases \
                 else field.get_sql(quote_char=quote_char, **kwargs)
 
diff --git a/pypika/terms.py b/pypika/terms.py
index fad1ba8..f7e9a94 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1,24 +1,26 @@
 import inspect
 import itertools
-import json
 import re
 from datetime import date
-
 from enum import Enum
+from typing import (
+    Iterable,
+    Union,
+)
 
 from pypika.enums import (
     Arithmetic,
     Boolean,
     Dialects,
     Equality,
+    JSONOperators,
     Matching,
-    PostgresOperators,
 )
 from pypika.utils import (
     CaseException,
-    QueryException,
     alias_sql,
     builder,
+    format_quotes,
     ignore_copy,
     resolve_is_aggregate,
 )
@@ -46,7 +48,8 @@ class Term:
     def tables_(self):
         return set()
 
-    def wrap_constant(self, val, wrapper_cls=None):
+    @staticmethod
+    def wrap_constant(val, wrapper_cls=None):
         """
         Used for wrapping raw inputs such as numbers in Criterions and Operator.
 
@@ -76,6 +79,20 @@ class Term:
         wrapper_cls = wrapper_cls or ValueWrapper
         return wrapper_cls(val)
 
+    @staticmethod
+    def wrap_json(val, wrapper_cls=None):
+        from .queries import QueryBuilder
+
+        if isinstance(val, (Term, QueryBuilder, Interval)):
+            return val
+        if val is None:
+            return NullValue()
+        if isinstance(val, (str, int, bool)):
+            wrapper_cls = wrapper_cls or ValueWrapper
+            return wrapper_cls(val)
+
+        return JSON(val)
+
     def replace_table(self, current_table, new_table):
         """
         Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.
@@ -215,12 +232,12 @@ class Term:
         return self.between(item.start, item.stop)
 
     def __str__(self):
-        return self.get_sql(quote_char='"')
+        return self.get_sql(quote_char='"', secondary_quote_char="'")
 
     def __hash__(self):
         return hash(self.get_sql(with_alias=True))
 
-    def get_sql(self):
+    def get_sql(self, **kwargs):
         raise NotImplementedError()
 
 
@@ -257,31 +274,96 @@ class ValueWrapper(Term):
     def fields(self):
         return []
 
-    def get_value_sql(self, quote_char=None, **kwargs):
+    def get_value_sql(self, **kwargs):
+        quote_char = kwargs.get('secondary_quote_char')
+
         # FIXME escape values
         if isinstance(self.value, Term):
-            return self.value.get_sql(quote_char=quote_char, **kwargs)
+            return self.value.get_sql(**kwargs)
         if isinstance(self.value, Enum):
             return self.value.value
         if isinstance(self.value, date):
-            return "'%s'" % self.value.isoformat()
+            value = self.value.isoformat()
+            return format_quotes(value, quote_char)
         if isinstance(self.value, basestring):
-            value = self.value.replace("'", "''")
-            return "'%s'" % value
+            value = self.value.replace(quote_char, quote_char * 2)
+            return format_quotes(value, quote_char)
         if isinstance(self.value, bool):
             return str.lower(str(self.value))
         if self.value is None:
             return 'null'
         return str(self.value)
 
-    def get_sql(self, quote_char=None, **kwargs):
-        sql = self.get_value_sql(quote_char=quote_char, **kwargs)
+    def get_sql(self, quote_char=None, secondary_quote_char="'", **kwargs):
+        sql = self.get_value_sql(quote_char=quote_char, secondary_quote_char=secondary_quote_char, **kwargs)
         return alias_sql(sql, self.alias, quote_char)
 
 
+class JSON(Term):
+    table = None
+
+    def __init__(self, value, alias=None):
+        super().__init__(alias)
+        self.value = value
+
+    def _recursive_get_sql(self, value, **kwargs):
+        if isinstance(value, dict):
+            return self._get_dict_sql(value, **kwargs)
+        if isinstance(value, list):
+            return self._get_list_sql(value, **kwargs)
+        if isinstance(value, str):
+            return self._get_str_sql(value, **kwargs)
+        return str(value)
+
+    def _get_dict_sql(self, value, **kwargs):
+        pairs = ['{key}:{value}'
+                     .format(key=self._recursive_get_sql(k, **kwargs),
+                             value=self._recursive_get_sql(v, **kwargs))
+                 for k, v in value.items()]
+        return ''.join(['{', ','.join(pairs), '}'])
+
+    def _get_list_sql(self, value, **kwargs):
+        pairs = [self._recursive_get_sql(v, **kwargs) for v in value]
+        return ''.join(['[', ','.join(pairs), ']'])
+
+    @staticmethod
+    def _get_str_sql(value, quote_char='"', **kwargs):
+        return format_quotes(value, quote_char)
+
+    def get_sql(self, secondary_quote_char="'", **kwargs):
+        return format_quotes(self._recursive_get_sql(self.value), secondary_quote_char)
+
+    def get_json_value(self, key_or_index: Union[str, int]):
+        return BasicCriterion(JSONOperators.GET_JSON_VALUE, self, self.wrap_constant(key_or_index))
+
+    def get_text_value(self, key_or_index: Union[str, int]):
+        return BasicCriterion(JSONOperators.GET_TEXT_VALUE, self, self.wrap_constant(key_or_index))
+
+    def get_path_json_value(self, path_json: str):
+        return BasicCriterion(JSONOperators.GET_PATH_JSON_VALUE, self, self.wrap_json(path_json))
+
+    def get_path_text_value(self, path_json: str):
+        return BasicCriterion(JSONOperators.GET_PATH_TEXT_VALUE, self, self.wrap_json(path_json))
+
+    def has_key(self, other):
+        return BasicCriterion(JSONOperators.HAS_KEY, self, self.wrap_json(other))
+
+    def contains(self, other):
+        return BasicCriterion(JSONOperators.CONTAINS, self, self.wrap_json(other))
+
+    def contained_by(self, other):
+        return BasicCriterion(JSONOperators.CONTAINED_BY, self, self.wrap_json(other))
+
+    def has_keys(self, other: Iterable):
+        return BasicCriterion(JSONOperators.HAS_KEYS, self, Array(*other))
+
+    def has_any_keys(self, other: Iterable):
+        return BasicCriterion(JSONOperators.HAS_ANY_KEYS, self, Array(*other))
+
+
 class Values(Term):
     def __init__(self, field, ):
-        super(Values, self).__init__(None)
+        super().__init__(None)
         self.field = Field(field) if not isinstance(field, Field) else field
 
     def get_sql(self, quote_char=None, **kwargs):
@@ -343,7 +425,7 @@ class EmptyCriterion:
         return other
 
 
-class Field(Criterion):
+class Field(Criterion, JSON):
     def __init__(self, name, alias=None, table=None):
         super(Field, self).__init__(alias)
         self.name = name
@@ -366,22 +448,19 @@ class Field(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the field with the tables replaced. 
+            A copy of the field with the tables replaced.
         """
         self.table = new_table if self.table == current_table else self.table
 
-    def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
+    def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, secondary_quote_char="'", **kwargs):
+        field_sql = format_quotes(self.name, quote_char)
+
         # Need to add namespace if the table has an alias
         if self.table and (with_namespace or self.table.alias):
-            field_sql = "{quote}{namespace}{quote}.{quote}{name}{quote}".format(
-                  namespace=self.table.alias or self.table._table_name,
-                  name=self.name,
-                  quote=quote_char or '',
-            )
-        else:
-            field_sql = "{quote}{name}{quote}".format(
-                  name=self.name,
-                  quote=quote_char or '',
+            field_sql = "{namespace}.{name}" \
+                .format(
+                  namespace=format_quotes(self.table.alias or self.table._table_name, quote_char),
+                  name=field_sql,
             )
 
         field_alias = getattr(self, 'alias', None)
@@ -390,6 +469,8 @@ class Field(Criterion):
 
         return alias_sql(field_sql, field_alias, quote_char)
 
+    # JSON functions (for PostgreSQL)
+
 
 class Star(Field):
     def __init__(self, table=None):
@@ -403,10 +484,9 @@ class Star(Field):
 
     def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
         if self.table and (with_namespace or self.table.alias):
-            return "{quote}{namespace}{quote}.*".format(
-                  namespace=self.table.alias or getattr(self.table, '_table_name'),
-                  quote=quote_char or ''
-            )
+            namespace = self.table.alias or getattr(self.table, '_table_name')
+            return "{}.*" \
+                .format(format_quotes(namespace, quote_char))
 
         return '*'
 
@@ -451,10 +531,11 @@ class Tuple(Criterion):
 
 class Array(Tuple):
     def get_sql(self, **kwargs):
-        if kwargs['dialect'] == Dialects.POSTGRESQL:
-            template = 'ARRAY[{}]'
-        else:
-            template = '[{}]'
+        dialect = kwargs.get('dialect', None)
+        template = 'ARRAY[{}]' \
+            if dialect in (Dialects.POSTGRESQL, Dialects.REDSHIFT) \
+            else '[{}]'
+
         return template.format(
               ','.join(term.get_sql(**kwargs)
                        for term in self.values)
@@ -473,84 +554,6 @@ class Bracket(Tuple):
         )
 
 
-class JSONField(Field):
-    DEFAULT_QUOTE_CHAR = '"'
-    EXPRESSION_QUOTE_CHAR = "'"
-
-    def __init__(self, val, alias=None, table=None, quote_char=DEFAULT_QUOTE_CHAR):
-        super().__init__(val, alias, table)
-        self.quote_char = quote_char
-
-    def get_sql(self, **kwargs):
-        if kwargs['dialect'] != Dialects.POSTGRESQL:
-            raise QueryException('Dialect `{}` does not support JSON field'.format(kwargs["dialect"]))
-
-        kwargs['quote_char'] = self.quote_char
-
-        # from __str__  sometimes, we can't get dialect, so if we can, we call .get_sql method
-        if hasattr(self.name, 'get_sql'):
-            name = self.name.get_sql(**kwargs)
-        else:
-            name = self.name
-
-        return '{quote}{val}{quote}'.format(
-              quote=self.quote_char,
-              val=name,
-        )
-
-    def has_key(self, other):
-        other = self._base_prepare(other)
-        return BasicCriterion(PostgresOperators.HAS_KEY, self, other)
-
-    def contains(self, other):
-        other = self._base_prepare(other)
-        return BasicCriterion(PostgresOperators.CONTAINS, self, other)
-
-    def contained_by(self, other):
-        other = self._base_prepare(other)
-        return BasicCriterion(PostgresOperators.CONTAINED_BY, self, other)
-
-    def has_keys(self, other):
-        if isinstance(other, list):
-            other = JSONField(Array(*other), quote_char='')
-        else:
-            raise QueryException('Type `{}` does not support for this operation'.format(type(other)))
-        return BasicCriterion(PostgresOperators.HAS_KEYS, self, other)
-
-    def has_any_keys(self, other):
-        if isinstance(other, list):
-            other = JSONField(Array(*other), quote_char='')
-        else:
-            raise QueryException('Type `{}` does not support for this operation'.format(type(other)))
-        return BasicCriterion(PostgresOperators.HAS_ANY_KEYS, self, other)
-
-    def get_value_by_key(self, other):
-        if isinstance(other, str):
-            other = JSONField(other, quote_char=self.EXPRESSION_QUOTE_CHAR)
-        elif isinstance(other, list) and len(other) == 2:
-            root_key = JSONField(other[0], quote_char=self.EXPRESSION_QUOTE_CHAR)
-            nested_key = JSONField(other[1], quote_char=self.EXPRESSION_QUOTE_CHAR)
-            return NestedCriterion(
-                  PostgresOperators.GET_JSON_VALUE, PostgresOperators.GET_TEXT_VALUE,
-                  self, root_key, nested_key,
-            )
-        else:
-            raise QueryException('Type `{}` does not support for this operation'.format(type(other)))
-        return BasicCriterion(PostgresOperators.GET_JSON_VALUE, self, other)
-
-    def _base_prepare(self, other):
-        if isinstance(other, str):
-            other = JSONField(other, quote_char=self.EXPRESSION_QUOTE_CHAR)
-        elif isinstance(other, (dict, list)):
-            other = JSONField(json.dumps(other), quote_char=self.EXPRESSION_QUOTE_CHAR)
-        elif isinstance(other, JSONField):
-            other.quote_char = self.EXPRESSION_QUOTE_CHAR
-        else:
-            raise QueryException('Type `{}` does not support'.format(type(other)))
-
-        return other
-
-
 class NestedCriterion(Criterion):
     def __init__(self, comparator, nested_comparator, left, right, nested, alias=None):
         super().__init__(alias)
@@ -581,7 +584,7 @@ class NestedCriterion(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.left = self.left.replace_table(current_table, new_table)
         self.right = self.right.replace_table(current_table, new_table)
@@ -639,7 +642,7 @@ class BasicCriterion(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.left = self.left.replace_table(current_table, new_table)
         self.right = self.right.replace_table(current_table, new_table)
@@ -647,11 +650,11 @@ class BasicCriterion(Criterion):
     def fields(self):
         return self.left.fields() + self.right.fields()
 
-    def get_sql(self, with_alias=False, **kwargs):
+    def get_sql(self, quote_char='"', with_alias=False, **kwargs):
         sql = '{left}{comparator}{right}'.format(
               comparator=self.comparator.value,
-              left=self.left.get_sql(**kwargs),
-              right=self.right.get_sql(**kwargs),
+              left=self.left.get_sql(quote_char=quote_char, **kwargs),
+              right=self.right.get_sql(quote_char=quote_char, **kwargs),
         )
         if with_alias and self.alias:
             return '{sql} "{alias}"'.format(sql=sql, alias=self.alias)
@@ -694,7 +697,7 @@ class ContainsCriterion(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.term = self.term.replace_table(current_table, new_table)
 
@@ -739,7 +742,7 @@ class BetweenCriterion(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.term = self.term.replace_table(current_table, new_table)
 
@@ -781,8 +784,8 @@ class BitwiseAndCriterion(Criterion):
 
     def get_sql(self, **kwargs):
         return "({term} & {value})".format(
-                term=self.term.get_sql(**kwargs),
-                value=self.value,
+              term=self.term.get_sql(**kwargs),
+              value=self.value,
         )
 
     def fields(self):
@@ -808,7 +811,7 @@ class NullCriterion(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.term = self.term.replace_table(current_table, new_table)
 
@@ -892,7 +895,7 @@ class ArithmeticExpression(Term):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the term with the tables replaced. 
+            A copy of the term with the tables replaced.
         """
         self.left = self.left.replace_table(current_table, new_table)
         self.right = self.right.replace_table(current_table, new_table)
@@ -944,7 +947,7 @@ class Case(Term):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the term with the tables replaced. 
+            A copy of the term with the tables replaced.
         """
         self._cases = [[criterion.replace_table(current_table, new_table), term.replace_table(current_table, new_table)]
                        for criterion, term
@@ -1046,7 +1049,7 @@ class Not(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.term = self.term.replace_table(current_table, new_table)
 
@@ -1095,7 +1098,7 @@ class Function(Criterion):
         :param new_table:
             The table to replace with.
         :return:
-            A copy of the criterion with the tables replaced. 
+            A copy of the criterion with the tables replaced.
         """
         self.args = [param.replace_table(current_table, new_table) for param in self.args]
 
diff --git a/pypika/utils.py b/pypika/utils.py
index f637e22..2d43733 100644
--- a/pypika/utils.py
+++ b/pypika/utils.py
@@ -66,7 +66,7 @@ def ignore_copy(func):
     """
 
     def _getattr(self, name):
-        if name in ['__copy__','__deepcopy__', '__getstate__', '__setstate__', '__getnewargs__']:
+        if name in ['__copy__', '__deepcopy__', '__getstate__', '__setstate__', '__getnewargs__']:
             raise AttributeError("'%s' object has no attribute '%s'" % (self.__class__.__name__, name))
 
         return func(self, name)
@@ -100,3 +100,10 @@ def alias_sql(sql, alias, quote_char=None):
     if alias is None:
         return sql
     return '{sql} {alias}'.format(sql=sql, alias=format_quotes(alias, quote_char))
+
+
+def validate(*args, exc=None, type=None):
+    if type is not None:
+        for arg in args:
+            if not isinstance(arg, type):
+                raise exc
diff --git a/requirements-dev.txt b/requirements-dev.txt
index 788a48a..e9c8edc 100644
--- a/requirements-dev.txt
+++ b/requirements-dev.txt
@@ -3,3 +3,4 @@ Sphinx==1.6.5
 sphinx-rtd-theme==0.2.4
 bumpversion==0.5.3
 parameterized==0.7.0
+typing==3.6.2
diff --git a/setup.py b/setup.py
index 3b39d72..2b25784 100644
--- a/setup.py
+++ b/setup.py
@@ -1,4 +1,5 @@
 import ast
+
 from setuptools import setup
 
 
@@ -15,7 +16,7 @@ def version():
             if len(node.targets) == 1:
                 name = node.targets[0]
                 if isinstance(name, ast.Name) and \
-                        name.id in ('__version__', '__version_info__', 'VERSION'):
+                      name.id in ('__version__', '__version_info__', 'VERSION'):
                     v = node.value
                     if isinstance(v, ast.Str):
                         return v.s
@@ -31,48 +32,52 @@ def version():
 
 
 setup(
-    # Application name:
-    name="PyPika",
-
-    # Version number:
-    version=version(),
-
-    # Application author details:
-    author="Timothy Heys",
-    author_email="theys@kayak.com",
-
-    # License
-    license='Apache License Version 2.0',
-
-    # Packages
-    packages=["pypika", "pypika.clickhouse"],
-
-    # Include additional files into the package
-    include_package_data=True,
-
-    # Details
-    url="https://github.com/kayak/pypika",
-
-    description="A SQL query builder API for Python",
-    long_description=readme(),
-
-    classifiers=[
-        'License :: OSI Approved :: Apache Software License',
-        'Development Status :: 5 - Production/Stable',
-        'Intended Audience :: Developers',
-        'Programming Language :: Python :: 3',
-        'Programming Language :: PL/SQL',
-        'Topic :: Software Development :: Libraries :: Python Modules',
-        'Topic :: Scientific/Engineering :: Information Analysis',
-        'Topic :: Scientific/Engineering :: Mathematics',
-        'Operating System :: POSIX',
-        'Operating System :: MacOS :: MacOS X',
-        'Operating System :: Microsoft :: Windows',
-    ],
-    keywords=('pypika python query builder querybuilder sql mysql postgres psql oracle vertica aggregated '
-              'relational database rdbms business analytics bi data science analysis pandas '
-              'orm object mapper'),
-
-    # Dependent packages (distributions)
-    test_suite="pypika.tests",
+      # Application name:
+      name="PyPika",
+
+      # Version number:
+      version=version(),
+
+      # Application author details:
+      author="Timothy Heys",
+      author_email="theys@kayak.com",
+
+      # License
+      license='Apache License Version 2.0',
+
+      # Packages
+      packages=["pypika", "pypika.clickhouse"],
+
+      # Include additional files into the package
+      include_package_data=True,
+
+      install_requires=[
+          'typing==3.6.2'
+      ],
+
+      # Details
+      url="https://github.com/kayak/pypika",
+
+      description="A SQL query builder API for Python",
+      long_description=readme(),
+
+      classifiers=[
+          'License :: OSI Approved :: Apache Software License',
+          'Development Status :: 5 - Production/Stable',
+          'Intended Audience :: Developers',
+          'Programming Language :: Python :: 3',
+          'Programming Language :: PL/SQL',
+          'Topic :: Software Development :: Libraries :: Python Modules',
+          'Topic :: Scientific/Engineering :: Information Analysis',
+          'Topic :: Scientific/Engineering :: Mathematics',
+          'Operating System :: POSIX',
+          'Operating System :: MacOS :: MacOS X',
+          'Operating System :: Microsoft :: Windows',
+      ],
+      keywords=('pypika python query builder querybuilder sql mysql postgres psql oracle vertica aggregated '
+                'relational database rdbms business analytics bi data science analysis pandas '
+                'orm object mapper'),
+
+      # Dependent packages (distributions)
+      test_suite="pypika.tests",
 )


Test patch:
diff --git a/pypika/tests/dialects/test_postgresql.py b/pypika/tests/dialects/test_postgresql.py
index 2f4e763..6363f4d 100644
--- a/pypika/tests/dialects/test_postgresql.py
+++ b/pypika/tests/dialects/test_postgresql.py
@@ -1,133 +1,180 @@
 import unittest
 
-from pypika import Table, Query, QueryException
+from pypika import (
+    JSON,
+    Table,
+)
 from pypika.dialects import PostgreSQLQuery
-from pypika.terms import JSONField
 
 
 class InsertTests(unittest.TestCase):
     table_abc = Table('abc')
-    json_field = JSONField('json')
 
     def test_array_keyword(self):
         q = PostgreSQLQuery.into(self.table_abc).insert(1, [1, "a", True])
 
         self.assertEqual('INSERT INTO "abc" VALUES (1,ARRAY[1,\'a\',true])', str(q))
 
-    def test_json_contains(self):
-        q = PostgreSQLQuery.from_(
-            self.table_abc,
-        ).select("*").where(
-            self.json_field.contains(
-                {"dates": "2018-07-10 - 2018-07-17"},
-            ),
-        )
 
-        self.assertEqual(
-            'SELECT * '
-            'FROM "abc" '
-            'WHERE "json"@>\'{"dates": "2018-07-10 - 2018-07-17"}\'',
-            str(q),
-        )
+class JSONObjectTests(unittest.TestCase):
+    def test_json_value_from_dict(self):
+        q = PostgreSQLQuery \
+            .select(JSON({"a": "foo"}))
 
-        q = PostgreSQLQuery.from_(
-            self.table_abc
-        ).select("*").where(
-            self.json_field.contains(
-                '{"dates": "2018-07-10 - 2018-07-17"}'
-            )
-        )
-        self.assertEqual(
-            'SELECT * '
-            'FROM "abc" '
-            'WHERE "json"@>\'{"dates": "2018-07-10 - 2018-07-17"}\'',
-            str(q),
-        )
+        self.assertEqual('SELECT \'{"a":"foo"}\'', str(q))
 
-    def test_json_has_key(self):
-        q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
-            self.json_field.has_key("dates"),
-        )
+    def test_json_value_from_array_num(self):
+        q = PostgreSQLQuery \
+            .select(JSON([1, 2, 3]))
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"?\'dates\'', str(q),
-        )
+        self.assertEqual('SELECT \'[1,2,3]\'', str(q))
 
-        q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
-            self.json_field.has_key(JSONField("dates")),
-        )
+    def test_json_value_from_array_str(self):
+        q = PostgreSQLQuery \
+            .select(JSON(['a', 'b', 'c']))
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"?\'dates\'', str(q),
-        )
+        self.assertEqual('SELECT \'["a","b","c"]\'', str(q))
 
-    def test_json_contained_by(self):
-        q = PostgreSQLQuery.from_(self.table_abc).select('*').where(
-            self.json_field.contained_by(
-                '{"dates": "2018-07-10 - 2018-07-17", "imported": "8"}',
-            ),
-        )
+    def test_json_value_from_dict_recursive(self):
+        q = PostgreSQLQuery \
+            .select(JSON({"a": 'z', "b": {"c": "foo"}, "d": 1}))
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"<@\'{"dates": "2018-07-10 - 2018-07-17", "imported": "8"}\'',
-            str(q),
-        )
+        # gotta split this one up to avoid the indeterminate order
+        sql = str(q)
+        start, end = 9, -2
+        self.assertEqual('SELECT \'{}\'', sql[:start] + sql[end:])
 
-        q = PostgreSQLQuery.from_(self.table_abc).select('*').where(
-            self.json_field.contained_by(
-                ["One", 'Two', "Three"],
-            ),
-        )
+        members_set = set(sql[start:end].split(','))
+        self.assertSetEqual({'"a":"z"', '"b":{"c":"foo"}', '"d":1'}, members_set)
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"<@\'["One", "Two", "Three"]\'', str(q),
-        )
 
-        q = PostgreSQLQuery.from_(self.table_abc).select('*').where(
-            self.json_field.contained_by('["One", "Two", "Three"]') & self.table_abc.id == 26
-        )
+class JSONOperatorsTests(unittest.TestCase):
+    # reference https://www.postgresql.org/docs/9.5/functions-json.html
+    table_abc = Table('abc')
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"<@\'["One", "Two", "Three"]\' AND "id"=26', str(q),
-        )
+    def test_get_json_value_by_key(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.get_json_value('dates'))
 
-    def test_json_has_keys(self):
-        q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
-            self.json_field.has_keys(['dates', 'imported']),
-        )
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"->\'dates\'', str(q))
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"?&ARRAY[\'dates\',\'imported\']', str(q)
-        )
+    def test_get_json_value_by_index(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.get_json_value(1))
 
-    def test_json_has_any_keys(self):
-        q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
-            self.json_field.has_any_keys(['dates', 'imported']),
-        )
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"->1', str(q))
 
-        self.assertEqual(
-            'SELECT * FROM "abc" WHERE "json"?|ARRAY[\'dates\',\'imported\']', str(q)
-        )
+    def test_get_text_value_by_key(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.get_text_value('dates'))
 
-    def test_get_value_by_key(self):
-        q = PostgreSQLQuery.from_(self.table_abc).select(self.json_field.get_value_by_key(['dates', 'import']))
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"->>\'dates\'', str(q))
 
-        self.assertEqual(
-            'SELECT "json"->\'dates\'->>\'import\' FROM "abc"', str(q)
-        )
+    def test_get_text_value_by_index(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.get_text_value(1))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"->>1', str(q))
+
+    def test_get_path_json_value(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.get_path_json_value('{a,b}'))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"#>\'{a,b}\'', str(q))
+
+    def test_get_path_text_value(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.get_path_text_value('{a,b}'))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"#>>\'{a,b}\'', str(q))
+
+
+class JSONBOperatorsTests(unittest.TestCase):
+    # reference https://www.postgresql.org/docs/9.5/functions-json.html
+    table_abc = Table('abc')
+
+    def test_json_contains_for_json(self):
+        q = PostgreSQLQuery \
+            .select(JSON({"a": 1, 'b': 2}).contains({"a": 1}))
 
-        q = PostgreSQLQuery.from_(self.table_abc).select(self.json_field.get_value_by_key('dates'))
+        # gotta split this one up to avoid the indeterminate order
+        sql = str(q)
+        start, end = 9, -13
+        self.assertEqual('SELECT \'{}\'@>\'{"a":1}\'', sql[:start] + sql[end:])
+
+        members_set = set(sql[start:end].split(','))
+        self.assertSetEqual({'"a":1', '"b":2'}, members_set)
+
+    def test_json_contains_for_field(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.contains({"dates": "2018-07-10 - 2018-07-17"}))
+
+        self.assertEqual('SELECT * '
+                         'FROM "abc" '
+                         'WHERE "json"@>\'{"dates":"2018-07-10 - 2018-07-17"}\'', str(q))
+
+    def test_json_contained_by_using_str_arg(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select('*') \
+            .where(self.table_abc.json.contained_by({"dates": "2018-07-10 - 2018-07-17", "imported": "8"}))
 
         self.assertEqual(
-            'SELECT "json"->\'dates\' FROM "abc"', str(q)
+              'SELECT * FROM "abc" '
+              'WHERE "json"<@\'{"dates":"2018-07-10 - 2018-07-17","imported":"8"}\'',
+              str(q),
         )
 
-    def test_not_valid_values(self):
-        q = Query.from_(self.table_abc).select("*").where(self.json_field.has_key("dates"))
-        self.assertRaises(QueryException, str, q)
+    def test_json_contained_by_using_list_arg(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select('*') \
+            .where(self.table_abc.json.contained_by(["One", 'Two', "Three"]))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"<@\'["One","Two","Three"]\'', str(q))
+
+    def test_json_contained_by_with_complex_criterion(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select('*') \
+            .where(self.table_abc.json.contained_by(["One", "Two", "Three"]) & (self.table_abc.id == 26))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"<@\'["One","Two","Three"]\' AND "id"=26', str(q))
 
-        self.assertRaises(QueryException, self.json_field.has_keys, 'dates')
-        self.assertRaises(QueryException, self.json_field.has_any_keys, 'dates')
-        self.assertRaises(QueryException, self.json_field.get_value_by_key, {'test':'test'})
-        self.assertRaises(QueryException, self.json_field.has_key, 1)
+    def test_json_has_key(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.has_key("dates"))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"?\'dates\'', str(q))
+
+    def test_json_has_keys(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.has_keys(['dates', 'imported']))
+
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"?&ARRAY[\'dates\',\'imported\']', str(q))
+
+    def test_json_has_any_keys(self):
+        q = PostgreSQLQuery \
+            .from_(self.table_abc) \
+            .select("*") \
+            .where(self.table_abc.json.has_any_keys(['dates', 'imported']))
 
+        self.assertEqual('SELECT * FROM "abc" WHERE "json"?|ARRAY[\'dates\',\'imported\']', str(q))