Repository: datajoint/datajoint-python (language: python)

Issue:
Upgrade documentation to new structure and fix regression with backslashes
This PR addresses the following:
- [x] Apply new documentation structure based on markdown
- [x] Fix bug related to unescaped backslashes in queries against string attributes
- Other minor fixes:
  - [x] Add pip package build and deploy to GHA
  - [x] Add GitHub release automation to GHA (must still edit the body on release highlights)
  - [x] Update expired NGINX certs
  - [x] Add `faker` to `local` docker environment
  - [x] Fix unused imports

Fix #999


Discussion / hints:

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 948d54766..2ba78a8d0 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,6 +1,7 @@
 ## Release notes
 
-### 0.14.0 -- TBA
+### 0.14.0 -- Feb 10, 2023
+- Added - `json` data type ([#245](https://github.com/datajoint/datajoint-python/issues/245)) PR [#1051](https://github.com/datajoint/datajoint-python/pull/1051)
 - Fixed - Activating a schema requires all tables to exist even if `create_tables=False` PR [#1058](https://github.com/datajoint/datajoint-python/pull/1058)
 - Changed - Populate call with `reserve_jobs=True` to exclude `error` and `ignore` keys - PR [#1062](https://github.com/datajoint/datajoint-python/pull/1062)
 - Added - Support for inserting data with CSV files - PR [#1067](https://github.com/datajoint/datajoint-python/pull/1067)
diff --git a/datajoint/condition.py b/datajoint/condition.py
index 034698925..80786c84c 100644
--- a/datajoint/condition.py
+++ b/datajoint/condition.py
@@ -8,8 +8,29 @@
 import decimal
 import numpy
 import pandas
+import json
 from .errors import DataJointError
 
+JSON_PATTERN = re.compile(
+    r"^(?P<attr>\w+)(\.(?P<path>[\w.*\[\]]+))?(:(?P<type>[\w(,\s)]+))?$"
+)
+
+
+def translate_attribute(key):
+    match = JSON_PATTERN.match(key)
+    if match is None:
+        return match, key
+    match = match.groupdict()
+    if match["path"] is None:
+        return match, match["attr"]
+    else:
+        return match, "json_value(`{}`, _utf8mb4'$.{}'{})".format(
+            *[
+                ((f" returning {v}" if k == "type" else v) if v else "")
+                for k, v in match.items()
+            ]
+        )
+
 
 class PromiscuousOperand:
     """
@@ -94,8 +115,19 @@ def make_condition(query_expression, condition, columns):
     from .expression import QueryExpression, Aggregation, U
 
     def prep_value(k, v):
-        """prepare value v for inclusion as a string in an SQL condition"""
-        if query_expression.heading[k].uuid:
+        """prepare SQL condition"""
+        key_match, k = translate_attribute(k)
+        if key_match["path"] is None:
+            k = f"`{k}`"
+        if (
+            query_expression.heading[key_match["attr"]].json
+            and key_match["path"] is not None
+            and isinstance(v, dict)
+        ):
+            return f"{k}='{json.dumps(v)}'"
+        if v is None:
+            return f"{k} IS NULL"
+        if query_expression.heading[key_match["attr"]].uuid:
             if not isinstance(v, uuid.UUID):
                 try:
                     v = uuid.UUID(v)
@@ -103,26 +135,36 @@ def prep_value(k, v):
                     raise DataJointError(
                         "Badly formed UUID {v} in restriction by `{k}`".format(k=k, v=v)
                     )
-            return "X'%s'" % v.bytes.hex()
+            return f"{k}=X'{v.bytes.hex()}'"
         if isinstance(
-            v, (datetime.date, datetime.datetime, datetime.time, decimal.Decimal)
+            v,
+            (
+                datetime.date,
+                datetime.datetime,
+                datetime.time,
+                decimal.Decimal,
+                list,
+            ),
         ):
-            return '"%s"' % v
+            return f'{k}="{v}"'
         if isinstance(v, str):
-            return '"%s"' % v.replace("%", "%%").replace("\\", "\\\\")
-        return "%r" % v
+            v = v.replace("%", "%%").replace("\\", "\\\\")
+            return f'{k}="{v}"'
+        return f"{k}={v}"
+
+    def combine_conditions(negate, conditions):
+        return f"{'NOT ' if negate else ''} ({')AND('.join(conditions)})"
 
     negate = False
     while isinstance(condition, Not):
         negate = not negate
         condition = condition.restriction
-    template = "NOT (%s)" if negate else "%s"
 
     # restrict by string
     if isinstance(condition, str):
         columns.update(extract_column_names(condition))
-        return template % condition.strip().replace(
-            "%", "%%"
+        return combine_conditions(
+            negate, conditions=[condition.strip().replace("%", "%%")]
         )  # escape %, see issue #376
 
     # restrict by AndList
@@ -139,7 +181,7 @@ def prep_value(k, v):
             return negate  # if any item is False, the whole thing is False
         if not items:
             return not negate  # and empty AndList is True
-        return template % ("(" + ") AND (".join(items) + ")")
+        return combine_conditions(negate, conditions=items)
 
     # restriction by dj.U evaluates to True
     if isinstance(condition, U):
@@ -151,23 +193,19 @@ def prep_value(k, v):
 
     # restrict by a mapping/dict -- convert to an AndList of string equality conditions
     if isinstance(condition, collections.abc.Mapping):
-        common_attributes = set(condition).intersection(query_expression.heading.names)
+        common_attributes = set(c.split(".", 1)[0] for c in condition).intersection(
+            query_expression.heading.names
+        )
         if not common_attributes:
             return not negate  # no matching attributes -> evaluates to True
         columns.update(common_attributes)
-        return template % (
-            "("
-            + ") AND (".join(
-                "`%s`%s"
-                % (
-                    k,
-                    " IS NULL"
-                    if condition[k] is None
-                    else f"={prep_value(k, condition[k])}",
-                )
-                for k in common_attributes
-            )
-            + ")"
+        return combine_conditions(
+            negate,
+            conditions=[
+                prep_value(k, v)
+                for k, v in condition.items()
+                if k.split(".", 1)[0] in common_attributes  # handle json indexing
+            ],
         )
 
     # restrict by a numpy record -- convert to an AndList of string equality conditions
@@ -178,12 +216,9 @@ def prep_value(k, v):
         if not common_attributes:
             return not negate  # no matching attributes -> evaluate to True
         columns.update(common_attributes)
-        return template % (
-            "("
-            + ") AND (".join(
-                "`%s`=%s" % (k, prep_value(k, condition[k])) for k in common_attributes
-            )
-            + ")"
+        return combine_conditions(
+            negate,
+            conditions=[prep_value(k, condition[k]) for k in common_attributes],
         )
 
     # restrict by a QueryExpression subclass -- trigger instantiation and move on
@@ -231,7 +266,11 @@ def prep_value(k, v):
         ]  # ignore False conditions
         if any(item is True for item in or_list):  # if any item is True, entirely True
             return not negate
-        return template % ("(%s)" % " OR ".join(or_list)) if or_list else negate
+        return (
+            f"{'NOT ' if negate else ''} ({' OR '.join(or_list)})"
+            if or_list
+            else negate
+        )
 
 
 def extract_column_names(sql_expression):
diff --git a/datajoint/declare.py b/datajoint/declare.py
index aadff9cdb..683e34759 100644
--- a/datajoint/declare.py
+++ b/datajoint/declare.py
@@ -7,6 +7,7 @@
 import logging
 from .errors import DataJointError, _support_filepath_types, FILEPATH_FEATURE_SWITCH
 from .attribute_adapter import get_adapter
+from .condition import translate_attribute
 
 UUID_DATA_TYPE = "binary(16)"
 MAX_TABLE_NAME_LENGTH = 64
@@ -23,6 +24,7 @@
         DECIMAL=r"(decimal|numeric)(\s*\(.+\))?(\s+unsigned)?$",
         FLOAT=r"(double|float|real)(\s*\(.+\))?(\s+unsigned)?$",
         STRING=r"(var)?char\s*\(.+\)$",
+        JSON=r"json$",
         ENUM=r"enum\s*\(.+\)$",
         BOOL=r"bool(ean)?$",  # aliased to tinyint(1)
         TEMPORAL=r"(date|datetime|time|timestamp|year)(\s*\(.+\))?$",
@@ -129,25 +131,9 @@ def build_attribute_parser():
     return attribute_name + pp.Optional(default) + colon + data_type + comment
 
 
-def build_index_parser():
-    left = pp.Literal("(").suppress()
-    right = pp.Literal(")").suppress()
-    unique = pp.Optional(pp.CaselessKeyword("unique")).setResultsName("unique")
-    index = pp.CaselessKeyword("index").suppress()
-    attribute_name = pp.Word(pp.srange("[a-z]"), pp.srange("[a-z0-9_]"))
-    return (
-        unique
-        + index
-        + left
-        + pp.delimitedList(attribute_name).setResultsName("attr_list")
-        + right
-    )
-
-
 foreign_key_parser_old = build_foreign_key_parser_old()
 foreign_key_parser = build_foreign_key_parser()
 attribute_parser = build_attribute_parser()
-index_parser = build_index_parser()
 
 
 def is_foreign_key(line):
@@ -275,7 +261,7 @@ def prepare_declare(definition, context):
                 foreign_key_sql,
                 index_sql,
             )
-        elif re.match(r"^(unique\s+)?index[^:]*$", line, re.I):  # index
+        elif re.match(r"^(unique\s+)?index\s*.*$", line, re.I):  # index
             compile_index(line, index_sql)
         else:
             name, sql, store = compile_attribute(line, in_key, foreign_key_sql, context)
@@ -449,10 +435,22 @@ def alter(definition, old_definition, context):
 
 
 def compile_index(line, index_sql):
-    match = index_parser.parseString(line)
+    def format_attribute(attr):
+        match, attr = translate_attribute(attr)
+        if match is None:
+            return attr
+        if match["path"] is None:
+            return f"`{attr}`"
+        return f"({attr})"
+
+    match = re.match(
+        r"(?P<unique>unique\s+)?index\s*\(\s*(?P<args>.*)\)", line, re.I
+    ).groupdict()
+    attr_list = re.findall(r"(?:[^,(]|\([^)]*\))+", match["args"])
     index_sql.append(
-        "{unique} index ({attrs})".format(
-            unique=match.unique, attrs=",".join("`%s`" % a for a in match.attr_list)
+        "{unique}index ({attrs})".format(
+            unique="unique " if match["unique"] else "",
+            attrs=",".join(format_attribute(a.strip()) for a in attr_list),
         )
     )
 
diff --git a/datajoint/expression.py b/datajoint/expression.py
index ab2d27eec..25dd2fe40 100644
--- a/datajoint/expression.py
+++ b/datajoint/expression.py
@@ -14,6 +14,7 @@
     assert_join_compatibility,
     extract_column_names,
     PromiscuousOperand,
+    translate_attribute,
 )
 from .declare import CONSTANT_LITERALS
 
@@ -342,6 +343,9 @@ def proj(self, *attributes, **named_attributes):
         from other attributes available before the projection.
         Each attribute name can only be used once.
         """
+        named_attributes = {
+            k: translate_attribute(v)[1] for k, v in named_attributes.items()
+        }
         # new attributes in parentheses are included again with the new name without removing original
         duplication_pattern = re.compile(
             rf'^\s*\(\s*(?!{"|".join(CONSTANT_LITERALS)})(?P<name>[a-zA-Z_]\w*)\s*\)\s*$'
diff --git a/datajoint/fetch.py b/datajoint/fetch.py
index cb5940e06..750939e5e 100644
--- a/datajoint/fetch.py
+++ b/datajoint/fetch.py
@@ -4,6 +4,7 @@
 import pandas
 import itertools
 import re
+import json
 import numpy as np
 import uuid
 import numbers
@@ -47,6 +48,8 @@ def _get(connection, attr, data, squeeze, download_path):
     """
     if data is None:
         return
+    if attr.json:
+        return json.loads(data)
 
     extern = (
         connection.schemas[attr.database].external[attr.store]
@@ -59,7 +62,6 @@ def _get(connection, attr, data, squeeze, download_path):
 
     if attr.is_filepath:
         return adapt(extern.download_filepath(uuid.UUID(bytes=data))[0])
-
     if attr.is_attachment:
         # Steps:
         # 1. get the attachment filename
diff --git a/datajoint/heading.py b/datajoint/heading.py
index db3b9a3cb..9a782fc0e 100644
--- a/datajoint/heading.py
+++ b/datajoint/heading.py
@@ -28,6 +28,7 @@
         numeric=None,
         string=None,
         uuid=False,
+        json=None,
         is_blob=False,
         is_attachment=False,
         is_filepath=False,
@@ -142,7 +143,7 @@ def non_blobs(self):
         return [
             k
             for k, v in self.attributes.items()
-            if not v.is_blob and not v.is_attachment and not v.is_filepath
+            if not (v.is_blob or v.is_attachment or v.is_filepath or v.json)
         ]
 
     @property
@@ -290,6 +291,7 @@ def _init_from_database(self):
                 ),
                 is_blob=bool(TYPE_PATTERN["INTERNAL_BLOB"].match(attr["type"])),
                 uuid=False,
+                json=bool(TYPE_PATTERN["JSON"].match(attr["type"])),
                 is_attachment=False,
                 is_filepath=False,
                 adapter=None,
@@ -375,10 +377,15 @@ def _init_from_database(self):
                 )
 
             if attr["in_key"] and any(
-                (attr["is_blob"], attr["is_attachment"], attr["is_filepath"])
+                (
+                    attr["is_blob"],
+                    attr["is_attachment"],
+                    attr["is_filepath"],
+                    attr["json"],
+                )
             ):
                 raise DataJointError(
-                    "Blob, attachment, or filepath attributes are not allowed in the primary key"
+                    "Json, Blob, attachment, or filepath attributes are not allowed in the primary key"
                 )
 
             if (
@@ -419,7 +426,8 @@ def _init_from_database(self):
         ):
             if item["Key_name"] != "PRIMARY":
                 keys[item["Key_name"]][item["Seq_in_index"]] = dict(
-                    column=item["Column_name"],
+                    column=item["Column_name"]
+                    or f"({item['Expression']})".replace(r"\'", "'"),
                     unique=(item["Non_unique"] == 0),
                     nullable=item["Null"].lower() == "yes",
                 )
diff --git a/datajoint/table.py b/datajoint/table.py
index 8ef0a5180..e047c8adc 100644
--- a/datajoint/table.py
+++ b/datajoint/table.py
@@ -8,6 +8,7 @@
 import uuid
 import csv
 import re
+import json
 from pathlib import Path
 from .settings import config
 from .declare import declare, alter
@@ -831,6 +832,8 @@ def __make_placeholder(self, name, value, ignore_extra_fields=False):
                 value = self.external[attr.store].upload_filepath(value).bytes
             elif attr.numeric:
                 value = str(int(value) if isinstance(value, bool) else value)
+            elif attr.json:
+                value = json.dumps(value)
         return name, placeholder, value
 
     def __make_row_to_insert(self, row, field_list, ignore_extra_fields):
diff --git a/docs/.docker/pip_requirements.txt b/docs/.docker/pip_requirements.txt
index 0ed6d8057..e09eef4cc 100644
--- a/docs/.docker/pip_requirements.txt
+++ b/docs/.docker/pip_requirements.txt
@@ -7,3 +7,4 @@ mdx-truly-sane-lists
 mkdocs-gen-files
 mkdocs-literate-nav
 mkdocs-exclude-search
+mkdocs-jupyter
diff --git a/docs/mkdocs.yaml b/docs/mkdocs.yaml
index 6a5f6883d..0a00bf067 100644
--- a/docs/mkdocs.yaml
+++ b/docs/mkdocs.yaml
@@ -15,7 +15,8 @@ nav:
   - Reproducibility:
       - Table Tiers: reproduce/table-tiers.md
       - Make Method: reproduce/make-method.md
-  - Tutorials: tutorials.md
+  - Tutorials:
+      - tutorials/json.ipynb
   - Develop: develop.md
   - Changelog: about/changelog.md
   - API: api/ # defer to gen-files + literate-nav
@@ -72,6 +73,8 @@ plugins:
       exclude:
         - "*/navigation.md"
         - "*/archive/*md"
+  - mkdocs-jupyter:
+      include: ["*.ipynb"]
 markdown_extensions:
   - attr_list
   - toc:
diff --git a/docs/src/concepts.md b/docs/src/concepts.md
deleted file mode 100644
index e69de29bb..000000000
diff --git a/docs/src/tutorials.md b/docs/src/tutorials.md
deleted file mode 100644
index 8b29d1afa..000000000
--- a/docs/src/tutorials.md
+++ /dev/null
@@ -1,3 +0,0 @@
-# Tutorials
-
-Coming soon!
diff --git a/docs/src/tutorials/json.ipynb b/docs/src/tutorials/json.ipynb
new file mode 100644
index 000000000..a0b0e01c3
--- /dev/null
+++ b/docs/src/tutorials/json.ipynb
@@ -0,0 +1,1073 @@
+{
+ "cells": [
+  {
+   "attachments": {},
+   "cell_type": "markdown",
+   "id": "7fe24127-c0d0-4ff8-96b4-6ab0d9307e73",
+   "metadata": {},
+   "source": [
+    "# Using the `json` type"
+   ]
+  },
+  {
+   "attachments": {},
+   "cell_type": "markdown",
+   "id": "67cf93d2",
+   "metadata": {},
+   "source": [
+    "First you will need to [install](../../getting-started/#installation) and [connect](../../getting-started/#connection) to a DataJoint [data pipeline](https://datajoint.com/docs/core/glossary/#data-pipeline).\n",
+    "\n",
+    "Now let's start by importing the `datajoint` client."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 2,
+   "id": "bc0b6f54-8f11-45f4-bf8d-e1058ee0056f",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "import datajoint as dj\n"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "3544cab9-f2db-458a-9431-939bea5affc5",
+   "metadata": {},
+   "source": [
+    "## Table Definition"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "a2998c71",
+   "metadata": {},
+   "source": [
+    "For this exercise, let's imagine we work for an awesome company that is organizing a fun RC car race across various teams in the company. Let's see which team has the fastest car! 🏎️\n",
+    "\n",
+    "This establishes 2 important entities: a `Team` and a `Car`. Normally we'd map this to their own dedicated table, however, let's assume that `Team` is well-structured but `Car` is less structured then we'd prefer. In other words, the structure for what makes up a *car* is varing too much between entries (perhaps because users of the pipeline haven't agreed yet on the definition? 🤷).\n",
+    "\n",
+    "This would make it a good use-case to keep `Team` as a table but make `Car` actually a `json` type defined within the `Team` table.\n",
+    "\n",
+    "Let's begin."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 3,
+   "id": "dc318298-b819-4f06-abbd-7bb7544dd431",
+   "metadata": {},
+   "outputs": [
+    {
+     "name": "stderr",
+     "output_type": "stream",
+     "text": [
+      "[2023-02-08 23:40:24,660][INFO]: Connecting root@fakeservices.datajoint.io:3306\n",
+      "[2023-02-08 23:40:25,454][INFO]: Connected root@fakeservices.datajoint.io:3306\n"
+     ]
+    }
+   ],
+   "source": [
+    "schema = dj.Schema(f\"{dj.config['database.user']}_json\")\n"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 4,
+   "id": "4aaf96db-85d9-4e94-a4c3-3558f4cc6671",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "@schema\n",
+    "class Team(dj.Lookup):\n",
+    "    definition = \"\"\"\n",
+    "    # A team within a company\n",
+    "    name: varchar(40)  # team name\n",
+    "    ---\n",
+    "    car=null: json  # A car belonging to a team (null to allow registering first but specifying car later)\n",
+    "    \n",
+    "    unique index(car.length:decimal(4, 1))  # Add an index if this key is frequently accessed\n",
+    "    \"\"\"\n"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "640bf7a7-9e07-4953-9c8a-304e55c467f8",
+   "metadata": {},
+   "source": [
+    "## Insert"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "7081e577",
+   "metadata": {},
+   "source": [
+    "Let's suppose that engineering is first up to register their car."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 5,
+   "id": "30f0d62e",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "Team.insert1(\n",
+    "    {\n",
+    "        \"name\": \"engineering\",\n",
+    "        \"car\": {\n",
+    "            \"name\": \"Rever\",\n",
+    "            \"length\": 20.5,\n",
+    "            \"inspected\": True,\n",
+    "            \"tire_pressure\": [32, 31, 33, 34],\n",
+    "            \"headlights\": [\n",
+    "                {\n",
+    "                    \"side\": \"left\",\n",
+    "                    \"hyper_white\": None,\n",
+    "                },\n",
+    "                {\n",
+    "                    \"side\": \"right\",\n",
+    "                    \"hyper_white\": None,\n",
+    "                },\n",
+    "            ],\n",
+    "        },\n",
+    "    }\n",
+    ")\n"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "ee5e4dcf",
+   "metadata": {},
+   "source": [
+    "Next, business and marketing teams are up and register their cars.\n",
+    "\n",
+    "A few points to notice below:\n",
+    "- The person signing up on behalf of marketing does not know the specifics of the car during registration but another team member will be updating this soon before the race.\n",
+    "- Notice how the `business` and `engineering` teams appear to specify the same property but refer to it as `safety_inspected` and `inspected` respectfully."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 6,
+   "id": "b532e16c",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "Team.insert(\n",
+    "    [\n",
+    "        {\n",
+    "            \"name\": \"marketing\",\n",
+    "            \"car\": None,\n",
+    "        },\n",
+    "        {\n",
+    "            \"name\": \"business\",\n",
+    "            \"car\": {\n",
+    "                \"name\": \"Chaching\",\n",
+    "                \"length\": 100,\n",
+    "                \"safety_inspected\": False,\n",
+    "                \"tire_pressure\": [34, 30, 27, 32],\n",
+    "                \"headlights\": [\n",
+    "                    {\n",
+    "                        \"side\": \"left\",\n",
+    "                        \"hyper_white\": True,\n",
+    "                    },\n",
+    "                    {\n",
+    "                        \"side\": \"right\",\n",
+    "                        \"hyper_white\": True,\n",
+    "                    },\n",
+    "                ],\n",
+    "            },\n",
+    "        },\n",
+    "    ]\n",
+    ")\n"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "57365de7",
+   "metadata": {},
+   "source": [
+    "We can preview the table data much like normal but notice how the value of `car` behaves like other BLOB-like attributes."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 7,
+   "id": "0e3b517c",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    <b>A team within a company</b>\n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car</p>\n",
+       "                            <span class=\"djtooltiptext\">A car belonging to a team (null to allow registering first but specifying car later)</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>marketing</td>\n",
+       "<td>=BLOB=</td></tr><tr><td>engineering</td>\n",
+       "<td>=BLOB=</td></tr><tr><td>business</td>\n",
+       "<td>=BLOB=</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 3</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name          car       \n",
+       "+------------+ +--------+\n",
+       "marketing      =BLOB=    \n",
+       "engineering    =BLOB=    \n",
+       "business       =BLOB=    \n",
+       " (Total: 3)"
+      ]
+     },
+     "execution_count": 7,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "Team()"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "c95cbbee-4ef7-4870-ad42-a60345a3644f",
+   "metadata": {},
+   "source": [
+    "## Restriction"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "8b454996",
+   "metadata": {},
+   "source": [
+    "Now let's see what kinds of queries we can form to demostrate how we can query this pipeline."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 8,
+   "id": "81efda24",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    <b>A team within a company</b>\n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car</p>\n",
+       "                            <span class=\"djtooltiptext\">A car belonging to a team (null to allow registering first but specifying car later)</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>business</td>\n",
+       "<td>=BLOB=</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 1</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name        car       \n",
+       "+----------+ +--------+\n",
+       "business     =BLOB=    \n",
+       " (Total: 1)"
+      ]
+     },
+     "execution_count": 8,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "# Which team has a `car` equal to 100 inches long?\n",
+    "Team & {'car.length': 100}"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 9,
+   "id": "fd7b855d",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    <b>A team within a company</b>\n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car</p>\n",
+       "                            <span class=\"djtooltiptext\">A car belonging to a team (null to allow registering first but specifying car later)</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>engineering</td>\n",
+       "<td>=BLOB=</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 1</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name          car       \n",
+       "+------------+ +--------+\n",
+       "engineering    =BLOB=    \n",
+       " (Total: 1)"
+      ]
+     },
+     "execution_count": 9,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "# Which team has a `car` less than 50 inches long?\n",
+    "Team & \"car->>'$.length' < 50\""
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 10,
+   "id": "b76ebb75",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    <b>A team within a company</b>\n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car</p>\n",
+       "                            <span class=\"djtooltiptext\">A car belonging to a team (null to allow registering first but specifying car later)</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>engineering</td>\n",
+       "<td>=BLOB=</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 1</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name          car       \n",
+       "+------------+ +--------+\n",
+       "engineering    =BLOB=    \n",
+       " (Total: 1)"
+      ]
+     },
+     "execution_count": 10,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "# Any team that has had their car inspected?\n",
+    "Team & [{'car.inspected:unsigned': True}, {'car.safety_inspected:unsigned': True}]"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 11,
+   "id": "b787784c",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    <b>A team within a company</b>\n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car</p>\n",
+       "                            <span class=\"djtooltiptext\">A car belonging to a team (null to allow registering first but specifying car later)</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>engineering</td>\n",
+       "<td>=BLOB=</td></tr><tr><td>marketing</td>\n",
+       "<td>=BLOB=</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 2</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name          car       \n",
+       "+------------+ +--------+\n",
+       "engineering    =BLOB=    \n",
+       "marketing      =BLOB=    \n",
+       " (Total: 2)"
+      ]
+     },
+     "execution_count": 11,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "# Which teams do not have hyper white lights for their first head light?\n",
+    "Team & {\"car.headlights[0].hyper_white\": None}"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "5bcf0b5d",
+   "metadata": {},
+   "source": [
+    "Notice that the previous query will satisfy the `None` check if it experiences any of the following scenarious:\n",
+    "- if entire record missing (`marketing` satisfies this)\n",
+    "- JSON key is missing\n",
+    "- JSON value is set to JSON `null` (`engineering` satisfies this)"
+   ]
+  },
+  {
+   "attachments": {},
+   "cell_type": "markdown",
+   "id": "bcf1682e-a0c7-4c2f-826b-0aec9052a694",
+   "metadata": {},
+   "source": [
+    "## Projection"
+   ]
+  },
+  {
+   "attachments": {},
+   "cell_type": "markdown",
+   "id": "daea110e",
+   "metadata": {},
+   "source": [
+    "Projections can be quite useful with the `json` type since we can extract out just what we need. This allows greater query flexibility but more importantly, for us to be able to fetch only what is pertinent."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 14,
+   "id": "8fb8334a",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    \n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car_name</p>\n",
+       "                            <span class=\"djtooltiptext\">calculated attribute</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car_length</p>\n",
+       "                            <span class=\"djtooltiptext\">calculated attribute</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>business</td>\n",
+       "<td>Chaching</td>\n",
+       "<td>100</td></tr><tr><td>engineering</td>\n",
+       "<td>Rever</td>\n",
+       "<td>20.5</td></tr><tr><td>marketing</td>\n",
+       "<td>None</td>\n",
+       "<td>None</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 3</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name          car_name     car_length    \n",
+       "+------------+ +----------+ +------------+\n",
+       "business       Chaching     100           \n",
+       "engineering    Rever        20.5          \n",
+       "marketing      None         None          \n",
+       " (Total: 3)"
+      ]
+     },
+     "execution_count": 14,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "# Only interested in the car names and the length but let the type be inferred\n",
+    "q_untyped = Team.proj(\n",
+    "    car_name='car.name',\n",
+    "    car_length=\"car.length\",\n",
+    ")\n",
+    "q_untyped"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 15,
+   "id": "bb5f0448",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "[{'name': 'business', 'car_name': 'Chaching', 'car_length': '100'},\n",
+       " {'name': 'engineering', 'car_name': 'Rever', 'car_length': '20.5'},\n",
+       " {'name': 'marketing', 'car_name': None, 'car_length': None}]"
+      ]
+     },
+     "execution_count": 15,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "q_untyped.fetch(as_dict=True)"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 16,
+   "id": "a307dfd7",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "\n",
+       "    \n",
+       "    <style type=\"text/css\">\n",
+       "        .Table{\n",
+       "            border-collapse:collapse;\n",
+       "        }\n",
+       "        .Table th{\n",
+       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
+       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
+       "        }\n",
+       "        .Table td{\n",
+       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
+       "        }\n",
+       "        .Table tr:nth-child(odd){\n",
+       "            background: #ffffff;\n",
+       "        }\n",
+       "        .Table tr:nth-child(even){\n",
+       "            background: #f3f1ff;\n",
+       "        }\n",
+       "        /* Tooltip container */\n",
+       "        .djtooltip {\n",
+       "        }\n",
+       "        /* Tooltip text */\n",
+       "        .djtooltip .djtooltiptext {\n",
+       "            visibility: hidden;\n",
+       "            width: 120px;\n",
+       "            background-color: black;\n",
+       "            color: #fff;\n",
+       "            text-align: center;\n",
+       "            padding: 5px 0;\n",
+       "            border-radius: 6px;\n",
+       "            /* Position the tooltip text - see examples below! */\n",
+       "            position: absolute;\n",
+       "            z-index: 1;\n",
+       "        }\n",
+       "        #primary {\n",
+       "            font-weight: bold;\n",
+       "            color: black;\n",
+       "        }\n",
+       "        #nonprimary {\n",
+       "            font-weight: normal;\n",
+       "            color: white;\n",
+       "        }\n",
+       "\n",
+       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
+       "        .djtooltip:hover .djtooltiptext {\n",
+       "            visibility: visible;\n",
+       "        }\n",
+       "    </style>\n",
+       "    \n",
+       "    \n",
+       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+       "        <table border=\"1\" class=\"Table\">\n",
+       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
+       "                            <p id=\"primary\">name</p>\n",
+       "                            <span class=\"djtooltiptext\">team name</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car_name</p>\n",
+       "                            <span class=\"djtooltiptext\">calculated attribute</span>\n",
+       "                        </div></th><th><div class=\"djtooltip\">\n",
+       "                            <p id=\"nonprimary\">car_length</p>\n",
+       "                            <span class=\"djtooltiptext\">calculated attribute</span>\n",
+       "                        </div> </th> </tr> </thead>\n",
+       "            <tbody> <tr> <td>business</td>\n",
+       "<td>Chaching</td>\n",
+       "<td>100.0</td></tr><tr><td>engineering</td>\n",
+       "<td>Rever</td>\n",
+       "<td>20.5</td></tr><tr><td>marketing</td>\n",
+       "<td>None</td>\n",
+       "<td>None</td> </tr> </tbody>\n",
+       "        </table>\n",
+       "        \n",
+       "        <p>Total: 3</p></div>\n",
+       "        "
+      ],
+      "text/plain": [
+       "*name          car_name     car_length    \n",
+       "+------------+ +----------+ +------------+\n",
+       "business       Chaching     100.0         \n",
+       "engineering    Rever        20.5          \n",
+       "marketing      None         None          \n",
+       " (Total: 3)"
+      ]
+     },
+     "execution_count": 16,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "# Nevermind, I'll specify the type explicitly\n",
+    "q_typed = Team.proj(\n",
+    "    car_name='car.name',\n",
+    "    car_length=\"car.length:float\",\n",
+    ")\n",
+    "q_typed"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 17,
+   "id": "8a93dbf9",
+   "metadata": {},
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "[{'name': 'business', 'car_name': 'Chaching', 'car_length': 100.0},\n",
+       " {'name': 'engineering', 'car_name': 'Rever', 'car_length': 20.5},\n",
+       " {'name': 'marketing', 'car_name': None, 'car_length': None}]"
+      ]
+     },
+     "execution_count": 17,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "q_typed.fetch(as_dict=True)"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "62dd0239-fa70-4369-81eb-3d46c5053fee",
+   "metadata": {},
+   "source": [
+    "## Describe"
+   ]
+  },
+  {
+   "attachments": {},
+   "cell_type": "markdown",
+   "id": "73d9df01",
+   "metadata": {},
+   "source": [
+    "Lastly, the `.describe()` function on the `Team` table can help us generate the table's definition. This is useful if we are connected directly to the pipeline without the original source."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 18,
+   "id": "0e739932",
+   "metadata": {},
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "# A team within a company\n",
+      "name                 : varchar(40)                  # team name\n",
+      "---\n",
+      "car=null             : json                         # A car belonging to a team (null to allow registering first but specifying car later)\n",
+      "UNIQUE INDEX ((json_value(`car`, _utf8mb4'$.length' returning decimal(4, 1))))\n",
+      "\n"
+     ]
+    }
+   ],
+   "source": [
+    "definition = Team.describe()\n",
+    "print(definition)"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "be1070d5-765b-4bc2-92de-8a6ffd885984",
+   "metadata": {},
+   "source": [
+    "## Cleanup"
+   ]
+  },
+  {
+   "attachments": {},
+   "cell_type": "markdown",
+   "id": "cb959927",
+   "metadata": {},
+   "source": [
+    "Finally, let's clean up what we created in this tutorial."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 19,
+   "id": "d9cc28a3-3ffd-4126-b7e9-bc6365040b93",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "schema.drop()\n"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "68ad4340",
+   "metadata": {},
+   "outputs": [],
+   "source": []
+  }
+ ],
+ "metadata": {
+  "kernelspec": {
+   "display_name": "Python 3",
+   "language": "python",
+   "name": "python3"
+  },
+  "language_info": {
+   "codemirror_mode": {
+    "name": "ipython",
+    "version": 3
+   },
+   "file_extension": ".py",
+   "mimetype": "text/x-python",
+   "name": "python",
+   "nbconvert_exporter": "python",
+   "pygments_lexer": "ipython3",
+   "version": "3.7.16"
+  },
+  "vscode": {
+   "interpreter": {
+    "hash": "949777d72b0d2535278d3dc13498b2535136f6dfe0678499012e853ee9abcab1"
+   }
+  }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 5
+}
diff --git a/requirements.txt b/requirements.txt
index 0872ba727..c0e8a99c2 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -4,7 +4,7 @@ pyparsing
 ipython
 pandas
 tqdm
-networkx<=2.6.3 # until py3.8 is our minimum version
+networkx<=2.6.3  # until py3.8 is our minimum version
 pydot
 minio>=7.0.0
 matplotlib


Test patch:
diff --git a/tests/test_json.py b/tests/test_json.py
new file mode 100644
index 000000000..b9b13e4ee
--- /dev/null
+++ b/tests/test_json.py
@@ -0,0 +1,219 @@
+import inspect
+from datajoint.declare import declare
+import datajoint as dj
+import numpy as np
+from distutils.version import LooseVersion
+from . import PREFIX
+
+if LooseVersion(dj.conn().query("select @@version;").fetchone()[0]) >= LooseVersion(
+    "8.0.0"
+):
+    schema = dj.Schema(PREFIX + "_json")
+    Team = None
+
+    def setup():
+        global Team
+
+        @schema
+        class Team(dj.Lookup):
+            definition = """
+            name: varchar(40)
+            ---
+            car=null: json
+            unique index(car.name:char(20))
+            uniQue inDex ( name, car.name:char(20), (json_value(`car`, _utf8mb4'$.length' returning decimal(4, 1))) )
+            """
+            contents = [
+                (
+                    "engineering",
+                    {
+                        "name": "Rever",
+                        "length": 20.5,
+                        "inspected": True,
+                        "tire_pressure": [32, 31, 33, 34],
+                        "headlights": [
+                            {
+                                "side": "left",
+                                "hyper_white": None,
+                            },
+                            {
+                                "side": "right",
+                                "hyper_white": None,
+                            },
+                        ],
+                    },
+                ),
+                (
+                    "business",
+                    {
+                        "name": "Chaching",
+                        "length": 100,
+                        "safety_inspected": False,
+                        "tire_pressure": [34, 30, 27, 32],
+                        "headlights": [
+                            {
+                                "side": "left",
+                                "hyper_white": True,
+                            },
+                            {
+                                "side": "right",
+                                "hyper_white": True,
+                            },
+                        ],
+                    },
+                ),
+                (
+                    "marketing",
+                    None,
+                ),
+            ]
+
+    def teardown():
+        schema.drop()
+
+    def test_insert_update():
+        car = {
+            "name": "Discovery",
+            "length": 22.9,
+            "inspected": None,
+            "tire_pressure": [35, 36, 34, 37],
+            "headlights": [
+                {
+                    "side": "left",
+                    "hyper_white": True,
+                },
+                {
+                    "side": "right",
+                    "hyper_white": True,
+                },
+            ],
+        }
+
+        Team.insert1({"name": "research", "car": car})
+        q = Team & {"name": "research"}
+        assert q.fetch1("car") == car
+
+        car.update({"length": 23})
+        Team.update1({"name": "research", "car": car})
+        assert q.fetch1("car") == car
+
+        try:
+            Team.insert1({"name": "hr", "car": car})
+            raise Exception("Inserted non-unique car name.")
+        except dj.DataJointError:
+            pass
+
+        q.delete_quick()
+        assert not q
+
+    def test_describe():
+        rel = Team()
+        context = inspect.currentframe().f_globals
+        s1 = declare(rel.full_table_name, rel.definition, context)
+        s2 = declare(rel.full_table_name, rel.describe(), context)
+        assert s1 == s2
+
+    def test_restrict():
+        # dict
+        assert (Team & {"car.name": "Chaching"}).fetch1("name") == "business"
+
+        assert (Team & {"car.length": 20.5}).fetch1("name") == "engineering"
+
+        assert (Team & {"car.inspected": "true"}).fetch1("name") == "engineering"
+
+        assert (Team & {"car.inspected:unsigned": True}).fetch1("name") == "engineering"
+
+        assert (Team & {"car.safety_inspected": "false"}).fetch1("name") == "business"
+
+        assert (Team & {"car.safety_inspected:unsigned": False}).fetch1(
+            "name"
+        ) == "business"
+
+        assert (Team & {"car.headlights[0].hyper_white": None}).fetch(
+            "name", order_by="name", as_dict=True
+        ) == [
+            {"name": "engineering"},
+            {"name": "marketing"},
+        ]  # if entire record missing, JSON key is missing, or value set to JSON null
+
+        assert (Team & {"car": None}).fetch1("name") == "marketing"
+
+        assert (Team & {"car.tire_pressure": [34, 30, 27, 32]}).fetch1(
+            "name"
+        ) == "business"
+
+        assert (
+            Team & {"car.headlights[1]": {"side": "right", "hyper_white": True}}
+        ).fetch1("name") == "business"
+
+        # sql operators
+        assert (Team & "`car`->>'$.name' LIKE '%ching%'").fetch1(
+            "name"
+        ) == "business", "Missing substring"
+
+        assert (Team & "`car`->>'$.length' > 30").fetch1("name") == "business", "<= 30"
+
+        assert (
+            Team & "JSON_VALUE(`car`, '$.safety_inspected' RETURNING UNSIGNED) = 0"
+        ).fetch1("name") == "business", "Has `safety_inspected` set to `true`"
+
+        assert (Team & "`car`->>'$.headlights[0].hyper_white' = 'null'").fetch1(
+            "name"
+        ) == "engineering", "Has 1st `headlight` with `hyper_white` not set to `null`"
+
+        assert (Team & "`car`->>'$.inspected' IS NOT NULL").fetch1(
+            "name"
+        ) == "engineering", "Missing `inspected` key"
+
+        assert (Team & "`car`->>'$.tire_pressure' = '[34, 30, 27, 32]'").fetch1(
+            "name"
+        ) == "business", "`tire_pressure` array did not match"
+
+        assert (
+            Team
+            & """`car`->>'$.headlights[1]' = '{"side": "right", "hyper_white": true}'"""
+        ).fetch1("name") == "business", "2nd `headlight` object did not match"
+
+    def test_proj():
+        # proj necessary since we need to rename indexed value into a proper attribute name
+        assert Team.proj(car_length="car.length").fetch(
+            as_dict=True, order_by="car_length"
+        ) == [
+            {"name": "marketing", "car_length": None},
+            {"name": "business", "car_length": "100"},
+            {"name": "engineering", "car_length": "20.5"},
+        ]
+
+        assert Team.proj(car_length="car.length:decimal(4, 1)").fetch(
+            as_dict=True, order_by="car_length"
+        ) == [
+            {"name": "marketing", "car_length": None},
+            {"name": "engineering", "car_length": 20.5},
+            {"name": "business", "car_length": 100.0},
+        ]
+
+        assert Team.proj(
+            car_width="JSON_VALUE(`car`, '$.length' RETURNING float) - 15"
+        ).fetch(as_dict=True, order_by="car_width") == [
+            {"name": "marketing", "car_width": None},
+            {"name": "engineering", "car_width": 5.5},
+            {"name": "business", "car_width": 85.0},
+        ]
+
+        assert (
+            (Team & {"name": "engineering"}).proj(car_tire_pressure="car.tire_pressure")
+        ).fetch1("car_tire_pressure") == "[32, 31, 33, 34]"
+
+        assert np.array_equal(
+            Team.proj(car_inspected="car.inspected").fetch(
+                "car_inspected", order_by="name"
+            ),
+            np.array([None, "true", None]),
+        )
+
+        assert np.array_equal(
+            Team.proj(car_inspected="car.inspected:unsigned").fetch(
+                "car_inspected", order_by="name"
+            ),
+            np.array([None, 1, None]),
+        )