Lists, structs, maps and JSON in KQL — with their meaning

Real data is rarely flat. A client has several branches, each with an address and a position on the map. A product carries a map of attributes. Until now, answering a question about what is inside such a value meant asking someone who knew the database's own tricks.

KQL now reads these values directly, with three small tools: selectors, lambdas and unnest. And because every part of a value can be described in the catalog, nothing is lost on the way in: a latitude is still degrees, a zip code is still text, and the answer carries the words your business uses.

Reaching in

Here is one client, written down as JSON. branches and attributes are columns of the table clients: the branches are a list of addresses, each with a position inside, and the attributes are a map. The database may hold the branches as a native list or as a JSON document. The selectors below read both the same way; lambdas and unnest need the native list.

{
  "id": 42,
  "name": "Hafenbäckerei",
  "branches": [
    { "street": "Hafenstrasse 4", "city": "Hamburg", "zip": "20457",
      "geo": { "lat": 53.54, "lon": 9.99 } },
    { "street": "Domshof 2", "city": "Bremen", "zip": "28195",
      "geo": { "lat": 53.08, "lon": 8.81 } },
    { "street": "Holstenstrasse 9", "city": "Kiel", "zip": "24103",
      "geo": { "lat": 54.32, "lon": 10.14 } }
  ],
  "attributes": { "segment": "retail" }
}

A selector, written @, picks a part out of a value:

FIND clients c
FETCH c.name, c.branches@1@city first_branch, c.branches@-1@city last_branch

c.branches@1@city is the city of the first branch, and c.branches@-1@city the city of the last one: positions count from 1, and a negative one counts from the end. A position that does not exist gives an empty value, not an error, and so does a client whose list is empty. The same @ goes deeper: c.branches@1@geo@lat is 53.54. It reads a struct by field name (@city) or by position (@1), and JSON the same way.

Maps work like lists and structs, with the key in place of the position: c.attributes@'segment' is retail, and a key that is not in the map gives an empty value. Keys can be text or whole numbers (@1). Not every database has a map type, and the reference lists which ones do.

A lambda says what to do with every element of a list. Added to the same query:

FETCH c.name, list_transform(c.branches, b -> b@city) cities

Read aloud, it is almost a sentence: for each branch b, take its city. The answer is Hamburg, Bremen, Kiel. list_filter keeps the elements that pass a test, and list_size counts them.

One row per element

To list the branches with their client, the list has to become rows. unnest does that:

FIND clients c, c unnest(c.branches) b
FETCH c.name, b.street, b.city

Each branch is now a row b, and its fields are columns: b.street, b.city, b.zip and b.geo, plus b.ordinal, its position in the list. The column headings are the labels from the catalog, not the technical names. A client with an empty list has no rows here. To keep it, write c + unnest(...): the client then appears once, with empty street and city.

Meaning travels with the parts

For a domain expert, the syntax matters less than this: the parts of a value are described, too. In the catalog, each part of a list, struct or map can carry what a plain column carries: a label, a comment, a unit, a quantity, how it should be shown. A latitude can be degrees, a zip code can be text that is never summed. A concept that is used in several places, say a position that appears in every branch and wherever else one is stored, is described only once.

Here is how the catalog describes the branches of our client: the type of each part, and the words the business uses for it. This excerpt shows two of the fields; street, city and lon are described in the same way:

{ "name": "branches",
  "concept": { "label": "Branches", "typeFamily": "ARRAY",
    "entries": {
      "element": { "label": "Branch", "typeFamily": "STRUCT", "entries": {
        "zip": { "label": "Zip code", "typeFamily": "TEXT", "dialectType": "VARCHAR",
                 "comment": "Postal code. Text, never summed." },
        "geo": { "label": "Position", "typeFamily": "STRUCT", "entries": {
          "lat": { "label": "Latitude", "typeFamily": "FLOAT", "dialectType": "DOUBLE", "unit": "deg" } } }
      } } } } }

A list has one entry, the element, a struct one entry for each field, and a map a key and a value. Give an entry a label, a comment or a unit, as for zip and lat, and it is described, once, in the catalog next to the table.

Whatever the catalog says about a part, the result says about any column made from it. That is why the unnest example above comes back with the headings "Street" and "City"; without the description they would be the technical names street and city.

The parts also speak your language. A German model can call the list filialen and the fields strasse, ort and position, so that a query on a table called kunden reads k.filialen@1@position@breitengrad. The person who owns the question writes and checks it in their own words.

Where it stops

Not every database can do everything with a nested value. Where one cannot, KQL says so, with a message and the place in the query. It never returns something that merely looks close. Three examples:

• A JSON array of objects is not a list, so list_size and lambdas are refused on it: "a list of objects stays JSON". The same branches stored as a native list answer both.
• Trino and Snowflake cannot count from the end inside JSON: "Trino has no position from the end in JSON: @-1". The same question asked from the start works.
• Oracle cannot tell the position of an element in an unnested collection: "'ordinal' is not available". The fields of the rows work.

These limits are written down in the tests, next to the queries that do work. The selector query of this post runs against real DuckDB, PostgreSQL, Oracle, Trino, Snowflake, MariaDB and SQLite databases, with the same rows for the native list and for JSON. The lambda and unnest examples run on the five of them that have native lists.

The same query in SQL

For the curious: how a column is stored matters, too. The catalog says whether the branches are a native list of structs or a JSON document, and koryki writes the SQL to match. This is the selector query from the start, for DuckDB.

DuckDB, native list of structs

SELECT c.name
, c.branches[1]['city'] AS first_branch
, c.branches[-1]['city'] AS last_branch
FROM clients c

DuckDB, JSON document

SELECT c.name
, (c.branches ->> '$[0].city') AS first_branch
, (c.branches ->> '$[#-1].city') AS last_branch
FROM clients c

Other databases get their own wording. PostgreSQL has to be told how long a native array is, and counts JSON positions from 0. Oracle cannot pick an element of a collection at all, so it numbers the rows instead. koryki takes care of all of it: nobody has to know this to ask the question.

Selectors, lambdas and unnest are available in koryki 0.4.0. The reference for nested values lists what each database supports.