Flattening Nested YAML Into Rows of Path and Value

A nested YAML file is a tree, and a lot of useful questions are easier to ask of a table. Which files set replicas? What is the value of env.LOG_LEVEL in staging and production? Which keys exist in one environment's file and not the other? A table of paths and values answers each with a short query. The conversion is a dozen lines, and the interesting parts are the decisions about lists, nulls and types. This post writes the flattener, runs it and loads the result into SQL. It belongs to the cluster on using YAML with databases. For the CSV-flavored version aimed at spreadsheets, see converting YAML to CSV for review.

A nested file

service:
  name: orders
  ports: [8080, 8443]
  env:
    LOG_LEVEL: info
    FEATURES: {search: true, export: false}
  replicas: 3
  owner: null

Four levels deep, with a list, a nested mapping, booleans and a null. A table wants one row per fact.

The flattener

A generator that recurses into mappings and lists and yields a row at each leaf:

import sys

import yaml


def rows(node, path=""):
    if isinstance(node, dict):
        for k, v in node.items():
            yield from rows(v, f"{path}.{k}" if path else str(k))
    elif isinstance(node, list):
        for i, v in enumerate(node):
            yield from rows(v, f"{path}[{i}]")
    else:
        yield path, type(node).__name__, node


doc = yaml.safe_load(open(sys.argv[1]))
for path, typ, value in rows(doc):
    print(f"{path:<24} {typ:<6} {value!r}")

On the file above it printed:

service.name             str    'orders'
service.ports[0]         int    8080
service.ports[1]         int    8443
service.env.LOG_LEVEL    str    'info'
service.env.FEATURES.search bool   True
service.env.FEATURES.export bool   False
service.replicas         int    3
service.owner            NoneType None

Eight leaves, eight rows. Four choices in the code are worth a sentence each.

Dotted paths for mappings, bracketed indexes for lists. service.ports[1] says both where the value lives and which element it was. This is the syntax JSON path tools use, so the same strings work in json_extract and similar functions.

The type is its own column. 8080 and '8080' print the same in many tools and are different facts. With the type recorded you can ask "where is port a string", which finds the quoting mistakes described in catching type mistakes in YAML early.

Nulls are rows. service.owner has type NoneType. A key with a null is different from a key that is absent: the first says "we thought about it and there is no owner", and the second says nothing. Keeping the row preserves that, and a comparison between two files can report the difference.

Empty containers vanish. An empty list or mapping has no leaves, so it yields no rows. That is a quiet loss: tags: [] and no tags key look the same in the output. If the distinction matters, emit a row for an empty container with type list and a value of [].

Keys containing dots or brackets would make the path ambiguous (a.b as one key or two). Use a safer delimiter such as / for data with arbitrary keys, or store the path as a JSON array.

Load the rows into a table

The generator feeds a table directly:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("create table kv (file text, path text, type text, value text)")
db.executemany("insert into kv values (?,?,?,?)",
               [("prod.yaml", p, t, repr(v)) for p, t, v in rows(doc)])

Values are stored as text with repr so strings, numbers and booleans share a column. Store the type beside it so you can cast back. With several files in one table (a file column), three useful queries become one-liners.

Where does a key appear?

select file, value from kv where path = 'service.replicas';

Which keys are in one file and not in another? This is a set difference on paths:

select path from kv where file = 'prod.yaml'
except
select path from kv where file = 'staging.yaml';

Which values differ? A self-join on path:

select a.path, a.value as prod, b.value as staging
from kv a join kv b on a.path = b.path
where a.file = 'prod.yaml' and b.file = 'staging.yaml' and a.value <> b.value;

We ran them on two files that differed in two places, a replicas of 3 against 1 and an owner key present in one and a debug key in the other. The first returned ('prod.yaml', '3') and ('staging.yaml', '1'), the second returned service.owner as the one path in production and not in staging, and the third returned ('service.replicas', '3', '1'). Each came back in a single statement. The three queries are the core of config comparison, and none needs a tree-diff library. They also work in PostgreSQL and DuckDB with the same SQL. The comparison of YAML files as text, with its false differences, is covered in diffing YAML files without whitespace noise.

The same thing inside SQL

When the YAML is already converted to JSON and in a database, you can flatten in SQL. SQLite's json_tree walks a document recursively and returns one row per node with its fullkey and type:

select fullkey, type, atom
from d, json_tree(d.doc)
where type not in ('object', 'array');

PostgreSQL has jsonb_path_query and recursive CTEs over jsonb_each for the same purpose, and DuckDB can unnest structs from a YAML file read with its YAML extension (using DuckDB to query data stored as YAML). The json_tree query listed the leaves with their fullkey, such as $.service.name, their type and their value, which matches the Python output apart from the $ prefix and SQLite's own type names (text, integer). The Python generator is the portable version, and the one you can extend, for instance to skip keys, redact secrets or add line numbers.

Extending it

A few additions make the flattener useful in real repositories.

Redaction. Mask any path matching password|secret|token before the row is stored, because you will end up sharing the table.

Line numbers. ruamel.yaml records line and column for each node, so a row can say where in the file it came from: a path is a name, and a line number is a link to the text.

Anchors. After safe_load an alias is a copy, so the same value appears under every path that used it. That is the right answer for "what does the program see", and the wrong one for "where is it defined".

Multi-document files. Use safe_load_all and add a document index as a column.

Limits. A pathological document, such as one built by aliases that expand repeatedly, could yield an enormous number of rows. Cap the count and the depth when the input is not yours; limiting alias expansion in a YAML parser covers it.

When flattening a YAML tree into rows loses too much

Structure is the point. A list of objects flattens into interleaved rows, servers[0].host, servers[0].port, servers[1].host. Reassembling an object needs the index parsing. If you want one row per server, use a record-oriented conversion, as in flattening nested JSON with pandas, and keep the nesting for what is truly nested.

Order matters. Rows have no order in SQL. List indexes preserve position, and mapping order is lost unless you add a sequence column.

The values are large. A multi-line string or a base64 blob in a value column makes the table awkward. Truncate in display, or keep such values out.

The question is about meaning, not text. "Do these two configs behave the same" depends on defaults and on the program. Rows compare what was written.

The language features behind the walk are in the Python documentation on yield from, and SQLite's json_tree in its JSON functions reference. To explore a document before flattening it, use the JSON viewer on its JSON form, or the YAML formatter.