<jupyter_start><jupyter_text>Querying JSON Databases This notebook demonstrates how to query hierarchical databases with our toy query language BQL.We start with loading a demo JSON dataset with data on departments and employees of the city of Chicago ([source](https://data.cityofchicago.org/Administration-Finance/Current-Employee-Names-Salaries-and-Position-Title/xzkq-xp2w)).  In general, we can treat any JSON document with a regular structure as a hierarchical database.<jupyter_code>%cd -q ..
from citydb_json import citydb<jupyter_output><empty_output><jupyter_text>This is the structure of the `citydb` database:```    {        "departments": [            {                "name": ...,                "employees": [                    {                        "name": ...,                        "surname": ...,                        "position": ...,                        "salary": ...                    },                    ... ]            },            ... ]    }```The top-level **City** object has the following fields:* `departments`: an array of department objects.**Department** objects have the following fields:* `name`: the name of the department.* `employees`: an array of employee objects.**Employee** objects have the following fields:* `name`: employee's first name.* `surname`: employee's last name.* `position`: employee's title.* `salary`: annual salary of the employee. Next, we import the BQL library.<jupyter_code>from bql import *<jupyter_output><empty_output><jupyter_text>The BQL query language is embedded in Python, which means any BQL query is a regular Python function which maps JSON input to JSON output.  We call such functions _JSON combinators_.Two trivial examples of JSON combinators are:* `Const(val)`, which maps all input to the same output value;* `Here()`, which returns its input unchanged.<jupyter_code>C = Const(42)
C(None), C(42), C([1, 2, 3])
I = Here()
I(None), I(42), I([1, 2, 3])<jupyter_output><empty_output><jupyter_text>More impressive is combinator `Field(name)` that extracts a field value from a JSON object.<jupyter_code>F = Field('x')
F({'x': 24, 'y': 42})<jupyter_output><empty_output><jupyter_text>By composing two field extractors, we can build a query that produces **the names of all departments**.<jupyter_code>Departments = Field('departments')
Name = Field('name')

Dept_Names = Departments >> Name
dept_names = Dept_Names(citydb)

dept_names [:5]<jupyter_output><empty_output><jupyter_text>What does the `>>` operator do exactly?  Fundamentally, `(A >> B)` composes `A` and `B` by sending the output of `A` to the input of `B`.$$(A \gg B):\; x \;\overset{A}{\longmapsto}\; y \;\overset{B}{\longmapsto}\; z \quad\text{(where $y = A(x),\, z = B(y)$)}$$However, if we directly apply this rule to evaluate the expression ``(Departments >> Name)(citydb)``, we will fail because `citydb['departments']['name']` does not exist.To make this work, we need to clarify the composition rule.  Namely, expression `(A >> B)(x)`, when `A(x)` is an array, applies `B` to _each_ element of the array.$$(A \gg B):\; x \;\overset{A}{\longmapsto}\; [y_1,\, y_2,\, \ldots] \;\overset{B}{\longmapsto}\; [z_1,\, z_2,\, \ldots] \quad\text{(when $A(x) = [y_1,\, y_2\, \ldots],\, B(y_k) = z_k$)}$$Moreover, when `B` itself produces array values, all `B` outputs are combined into one array, which becomes the output of `(A >> B)`.$$(A \gg B):\; x \;\overset{A}{\longmapsto}\; [y_1,\, y_2,\, \ldots] \;\overset{B}{\longmapsto}\; [z_{11},\, z_{12},\, \ldots\, z_{21},\, z_{22},\, \ldots] \quad\text{(when also $B(y_k)$ are arrays $[z_{k1},\, z_{k2},\, \ldots]$)}$$The last feature is used when we list **the names of all employees**.<jupyter_code>Employees = Field('employees')

Empl_Names = Departments >> Employees >> Name
empl_names = Empl_Names(citydb)

empl_names [:5]<jupyter_output><empty_output><jupyter_text>Dual to `Field(name)`, combinator `Select(...)` *constructs* JSON objects.  Parameters of `Select(...)` are combinators that construct object fields.  Here is a trivial example.<jupyter_code>S = Select(x=Const(42), y=Here())
S(24)<jupyter_output><empty_output><jupyter_text>Let us use `Select(...)` to generate **the name and the number of employees for each department**.<jupyter_code>Depts_With_Size = Departments >> Select(name=Name, size=Count(Employees))
depts_with_size = Depts_With_Size(citydb)

depts_with_size [:5]<jupyter_output><empty_output><jupyter_text>Here, combinator `Count(Employees)` returns the length of the `employees` array.  In general, `Count(F)` lets `F` process its input expecting the output of `F` to be an array, then returns the length of the array.$$\operatorname{Count}(F):\; x \;\overset{F}{\longmapsto}\; [y_1,\, y_2,\, \ldots\, y_N] \;\overset{\operatorname{len}}{\longmapsto}\; N$$(You may've expected `Employees >> Count()`, but that'd make operator `>>` non-associative).Array combinators such as `Count(...)` are called *aggregate combinators*.  The following aggregate combinators are defined in BQL: `Count()`, `Min()`, `Max()`, `First()`.<jupyter_code>Num_Depts = Count(Departments)
Num_Depts(citydb)
Salary = Field('salary')

Top_Salary = Max(Departments >> Employees >> Salary)
Top_Salary(citydb)
One_Empl = First(Departments >> Employees)
One_Empl(citydb)
Three_Depts = First(Departments >> Name, Const(3))
Three_Depts(citydb)
Half_Depts = First(Departments >> Name, Count(Departments)//2)
Half_Depts(citydb)<jupyter_output><empty_output><jupyter_text>Combinator `Filter(P)` applies predicate `P` to its input.  If the predicate condition is not satisfied, the input is dropped, otherwise it is returned unchanged.  Let us use `Filter()` to find **the departments with more than 1000 employees**.<jupyter_code>Size = Field('size')

Large_Depts = Depts_With_Size >> Filter(Size > 1000)
Large_Depts(citydb)<jupyter_output><empty_output><jupyter_text>Here, combinator `Depts_With_Size`, which adds `size` field to each department object, is composed with combinator `Filter(Size > 1000)`, which gathers the departments that satisfy condition `Size > 1000`.In the following example, we use `Filter()` to find **the number of employees whose annual salary exceeds 200k**.<jupyter_code>Num_Well_Paid_Empls = \
    Count(Departments >> Employees >> Filter(Salary >= 200000))
Num_Well_Paid_Empls(citydb)<jupyter_output><empty_output><jupyter_text>Now suppose we'd like to find **the number of employees with salary in a certain range**, but we don't know the range in advance.  In this case, we can construct a *parameterized query*.<jupyter_code>Min_Salary = Ref('min_salary')
Max_Salary = Ref('max_salary')

Num_Empls_By_Salary_Range = \
    Count(Departments >> Employees >> Filter((Salary >= Min_Salary) & (Salary < Max_Salary)))<jupyter_output><empty_output><jupyter_text>To run the `Num_Empls_By_Salary_Range` query, we need to supply it with parameters `min_salary` and `max_salary`.<jupyter_code>Num_Empls_By_Salary_Range(citydb, {'min_salary': 200000, 'max_salary': 1000000})
Num_Empls_By_Salary_Range(citydb, {'min_salary': 100000, 'max_salary': 200000})
Num_Empls_By_Salary_Range(citydb, {'min_salary': 0, 'max_salary': 100000})<jupyter_output><empty_output><jupyter_text>The query knows which parameters it needs.<jupyter_code>Num_Empls_By_Salary_Range.refs()<jupyter_output><empty_output><jupyter_text>The last feature we discuss here is an ability to assign parameter values dynamically.Consider a query: find **the top salary for each department**.  It could be easily implemented using `Max()` aggregate.<jupyter_code>Depts_With_Max_Salary = \
    Departments >> Select(name=Name, max_salary=Max(Employees >> Salary))
Depts_With_Max_Salary(citydb) [:5]<jupyter_output><empty_output><jupyter_text>Now let us ask a slightly different question: find **the employees with the highest salary at their department**.  We may try to use the `Filter()` combinator as follows.<jupyter_code>Highest_Paid_Empls_By_Dept = \
    Departments >> Employees >> Filter(Salary == Max_Salary)<jupyter_output><empty_output><jupyter_text>But the filter condition `(Salary == Max_Salary)` is problematic since we cannot supply `max_salary` as a query parameter.  Instead it must be calculated dynamically for each department.  The `Given(...)` combinator does exactly that.<jupyter_code>Highest_Paid_Empls_By_Dept = \
    Departments >> \
    Given(
        Employees >> Filter(Salary == Max_Salary),
        max_salary=Max(Employees >> Salary))
    
Highest_Paid_Empls_By_Dept(citydb) [:5]<jupyter_output><empty_output><jupyter_text>Notably, `Highest_Paid_Empls_By_Dept` requires no parameters despite the fact that its definition refers to `max_salary`.<jupyter_code>Highest_Paid_Empls_By_Dept.refs()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13642611683848796, 'fraction_numerical': 0.012027491408934709, 'mean_word_length': 5.209815078236131, 'pattern_counts': {'":': 7, '<': 74, '<?xml version=': 0, '>': 134, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '14175', 'license': 'Apache-2.0', 'n_tokens_mistral': 2969, 'n_tokens_neox': 2750, 'n_words': 831, 'path': 'py/demo/bql-demo.ipynb', 'repo_name': 'rbt-lang/rbt-proto', 'size': '8730'}