RAG over structured data: CSV and JSON

What you'll learn

• Choose which fields of a table row to embed and which to keep as metadata.
• Flatten a nested JSON record without losing what you need to filter on.
• Diagnose four question types that retrieval answers fluently and wrongly.
• Route a question about a table to search, a filter, a lookup or a query engine.

Lessons 1 to 3 of T6.4 worked on prose: handbook paragraphs and PDF pages. Larkspur also has a server catalogue, a table of ten sizes with vCPUs, memory, a monthly price and a one-line description. Ask What is the average monthly price of a Larkspur server? and a RAG chain retrieves four rows and averages those, confidently and wrongly.

A table row is a set of typed fields; an embedding model takes one string. Something has to turn the row into text, and that choice, the record representation, decides what the index can find. Notebook 04a shows three ways to write the same row. For the ls-mem-4 row:

Table 1: Three representations of one catalogue row.

04a's rule picks the third and moves everything else to metadata. Free text carries meaning and belongs in the embedded text; identifiers, categories, numbers and dates are things you filter on, and belong in metadata, where a filter compares them exactly:

Figure 1: One row, split by job.

The module's lab measures what the choice does, with real all-MiniLM-L6-v2 scores:

Table 2: Two representations under two questions (the module's sample-or-population lab).

Two details make the split work. 04a casts every number to an integer before it goes into metadata, because a range filter compares numbers and a CSV loader returns strings (lesson 3 of T5.2). And the context sent to the model adds the metadata back after each description, so the model can quote a price it could not search on.

The rule is a default. "Built in 1962" carries meaning a buyer might search for, in a way "295000" does not. Judge each field by whether its value has meaning or only magnitude.

JSON adds depth. A support ticket in 04a has a nested customer object, a list of tags and a list of messages. Its conversion makes two separate decisions:

• Meaning goes to the embedded text: the subject and the conversation, one "sender: body" line per message.
• Filters go to flat metadata: the customer's tier becomes a top-level customer-tier field, because Chroma rejects nested objects in metadata.

Lists are the hard case. 04a joins the tags into one string, "export,performance,timeout", and an equality filter on that string matches only the whole string in that order. Check what your store accepts before flattening: the Chroma version installed in this course (1.5.9) stores a list value as metadata, and its contains operator matches list membership. In a scratch test it returned the record whose tags were the list export and performance, and not the one holding the single string "export,timeout".

Where the store has no list type, the options are a boolean field per tag, or a relational table for tags with only ids in the vector store. When filtering is that relational, the vector store is the wrong primary home for the data.

The optional notebooks show the two ends. 04b loads a customer CSV with every column in the text and asks which company does Sheryl Baxter work for?, an exact lookup by name. 04c uses JSON path expressions to pick the fields of each Nobel Prize record (year, category, laureate names) that become text before embedding.

Retrieval returns the k rows most similar to the question. Four kinds of question need something else, and on a table they look like ordinary questions:

Table 3: Four question types retrieval cannot answer.

The interactive sample-or-population lab runs all four on the server catalogue, with description-only text:

Table 4: Three questions in the lab.

Each of these comes back as a fluent, specific answer. We call this the sample average: an answer computed over the rows retrieval happened to return, presented as a fact about the whole table. 04a's comparison names the difference between the two approaches: query generation fails loudly (a bad query raises an error or returns nothing), and retrieval fails quietly (it answers from whatever it retrieved).

So route by the kind of question, not the kind of data:

Figure 2: Route a table question by what it needs.

04a builds each branch in its simplest form:

• Router: one prompt classifies a question as aggregate, lookup or semantic, and the system refuses the first two instead of answering from a sample.
• Self-query: a structured-output call turns Which properties cost less than $400,000? into a semantic part and, when extraction works, a maximum price of 400,000, which becomes a store filter. The notebook's filter uses less-than-or-equal, which lets a listing at exactly $400,000 through; "less than" needs strictly-less-than. The full version is T6.2's self-querying lesson.
• Query generation: the model writes one line of pandas over the schema, and the notebook evaluates it.

Pitfall: evaluating model-written code. That last branch runs code the model wrote with full interpreter access, and a data field can carry a prompt injection. In production, generate SQL against a read-only connection with a timeout, or run a sandboxed executor.

1. Embed descriptions only; keep name, vCPUs, memory and price as typed metadata.
2. Route averages and rankings to SQL over the catalogue, read-only.
3. Route "ls-cpu-8" to a get by id, and price limits to a self-query filter with the right operator.
4. Send semantic search only the descriptive questions, such as a server for a memory-hungry database.

Lesson 5 of T6.4 keeps the same pipeline and moves it onto your own hardware.

The code for this lesson is in these notebooks. Colab runs one in your browser: install the packages its first cell imports and set the API keys it reads. To run every notebook as written, clone the repository and use its uv environment.

• Embed fields whose values carry meaning, and put identifiers, numbers and dates in metadata, cast to their real types. Embedding every column dilutes the vector (field dilution).
• Retrieval reads a sample of k rows. Aggregation, numeric comparison, exact lookup and ranking need every row, a filter or an id, and retrieval fails on them quietly: the catalogue's average comes back as $36.25 instead of $102.50.
• Route by the kind of question: a query engine for aggregates, get-by-id for lookups, self-query filters for ranges, semantic search for the rest. Never evaluate model-written code without a sandbox.