JSON and Semi-structured Data experimental

JSON and Semi-structured Data Data that does not fit a table: documents and trees, JSONPath, schema validation, and querying JSON in place with SQLite. Documents and trees. Documents and trees. Distinguish document and tree representations of data and model a dataset using them.. Documents, Nesting, Arrays, When trees beat tables Documents A relational table forces every row to share the same fixed columns. A document drops that constraint: it is a self-contained, nested bundle of key/value data that can carry an optional field on one record and a completely different one on the next. JavaScript Object Notation (JSON) is the dominant document format because it maps directly onto three building blocks every language already has: objects (unordered key/value maps), arrays (ordered lists), and scalars (strings, numbers, booleans, null). A customer document might hold name, email, an array of orders, and a preferences object that varies wildly from customer to customer. There is no schema migration required to add a field to one document without touching the rest of the collection, which is the property that makes documents attractive for data whose shape is still evolving. Nesting Documents become trees the moment a value is itself an object or array. A customer document with an address object, which itself has a geo object with lat/lng, is three levels deep. Nesting lets you co-locate data that is always read together (an order and its line items, a blog post and its comments), so a single fetch returns the whole unit with no join. The cost is that nested data is harder to query in isolation and harder to keep consistent when the same fact (a customer's name) is duplicated across many nested copies. Arrays Arrays represent one-to-many relationships inline: tags: ["urgent", "billing"], or line_items: [{...}, {...}]. Unlike a relational child table, an array has position and order built in, but no independent identity or foreign key: an array element only exists inside its parent document. This makes arrays ideal for ordered, bounded, always-fetched-together collections, and a poor fit for collections that grow without bound or need to be queried across many parent documents efficiently. When trees beat tables Trees win when the data is naturally hierarchical and heterogeneous: configuration files, API payloads, event logs, product catalogs with wildly different attributes per category. Tables win when you need strong cross-record consistency, frequent updates to shared sub-parts, or efficient queries that filter and aggregate across many records' internal fields. In practice, most systems are hybrid: a relational core holds identity and referential integrity, while a JSON column holds the flexible, document-shaped extras. Recognizing which parts of your data are tabular and which are document-shaped is the central modelling skill of this module. A useful diagnostic when facing a new dataset is to ask two questions in order: does every record share the same fields, and does the data need to support ad hoc filtering and aggregation across many records at once. Two yes answers point toward a table; a no to either points toward a document, and a data model that tries to force a genuinely heterogeneous, deeply nested dataset into a rigid table schema typically ends up either dropping information that does not fit the columns or exploding into dozens of sparsely populated optional columns, both worse outcomes than simply choosing the document shape from the start. Related CCI capabilities The decision to model a dataset as documents or as relational tables is a design-time control, not a stylistic preference: getting it wrong early forces expensive rework once sensitive fields are already nested deep inside a live schema. CCI's Secure Software Development Life Cycle (SDLC) at Scale engagement (https://www.cambridgecyberinternational.com/en/services/secure-sdlc/) treats this kind of data-shape decision as part of the software lifecycle rather than an afterthought, reviewing modelling choices alongside the rest of the design before they harden into production schemas. JSONPath and querying. JSONPath and querying. Explain JSONPath syntax and write queries that navigate a JSON document.. JSONPath, Filters, SQLite JSON functions, Indexing JSON JSONPath JSONPath is to JavaScript Object Notation (JSON) what a file path is to a filesystem: a compact string that names a location inside a tree. The root is $. Descend into an object field with dot notation, $.address.city, or bracket notation, $['address']['city']. Index into an array with $.orders[0] for the first element; RFC 9535 defines negative index selectors, so $.orders[-1] selects the last element (though SQLite's own json_extract path-string parser does not support negative indices). SQLite's json_extract(doc, '$.address.city') takes a JSON document and a path and returns the scalar or sub-document at that location, or NULL if the path does not exist. The shorthand doc->>'$.address.city' operator does the same extraction and unquotes text results, while doc->'$.address.city' keeps the result as JSON (useful when the target is itself an object or array you want to pass to another JSON function). Filters Plain paths only reach fixed locations. To select array elements by condition, JSONPath filter expressions use a ? filter selector: $.orders[?@.qty > 5] selects every order object whose qty field exceeds 5, where @ refers to the current element being tested (the older, widely-used ?(...) form with parentheses around the whole expression is common in libraries such as jsonpath-plus but is not the RFC 9535 grammar, which allows parentheses only around sub-expressions). Full filter-expression JSONPath is not implemented by SQLite's built-in json_extract; instead, SQLite exposes json_each and json_tree as table-valued functions that unnest a JSON array or object into rows, which you then filter with ordinary Structured Query Language (SQL) WHERE clauses: arguably more powerful, since you get the whole SQL language rather than a narrow filter grammar. SQLite JSON functions The core functions: json_extract(j, path) pulls a value out; json_valid(j) checks well-formedness; json_type(j, path) reports the type at a path (object, array, text, integer, real, true, false, null); json_array_length(j, path) counts array elements; json_set/json_insert/json_replace modify a document and return the new JSON text; json_remove deletes a path. json_each(j, path) and json_tree(j, path) are table-valued functions: json_each yields one row per direct child (with columns key, value, type, atom, id, parent, fullkey, path), while json_tree recurses into every descendant. Joining json_each against a table of documents is the standard way to query inside arrays with full SQL predicates, aggregates, and joins. Indexing JSON Extracting a JSON field on every query is slow at scale because SQLite must parse the text each time. A generated column solves this: ALTER TABLE docs ADD COLUMN city TEXT GENERATED ALWAYS AS (json_extract(doc, '$.address.city')) VIRTUAL; then CREATE INDEX idx_city ON docs(city);. The query planner can now use the index for WHERE city = 'Paris' without touching the JSON parser, provided the query references the generated column (or SQLite's expression-index matching recognizes the equivalent json_extract expression directly in an index definition, which is also supported). Generated columns are not free: each one adds a small write-time cost to compute and store the extracted value, and an index on a rarely-queried field is pure overhead with no compensating read benefit, so the decision to add one should follow from an actual slow query, not a precautionary instinct to index every field a report might someday filter on. A practical workflow is to build the document-only schema first, observe which WHERE clauses show up in slow-query logs, and add generated columns and indexes only for the fields that queries actually filter on in practice. Related CCI capabilities Query surfaces built on JSONPath, json_each, and generated columns expand what an application can ask of stored data, and every one of those surfaces is also a potential access point if the underlying store is reachable without authentication. CCI's Secure Software Development Life Cycle (SDLC) at Scale engagement (https://www.cambridgecyberinternational.com/en/services/secure-sdlc/) reviews how document and JSON query interfaces are exposed and access-controlled across the development lifecycle, not just how efficiently they run. Validation. Validation. Explain schema constraints and configure validation to check documents against them.. JSON Schema, Types and constraints, Error messages, Evolution JSON Schema JSON Schema, itself a JavaScript Object Notation (JSON) document, describes the shape another JSON document must have. The top-level keyword "type" names the expected JSON type (object, array, string, number, integer, boolean, null), "properties" maps field names to their own sub-schemas, and "required" lists field names that must be present. A minimal schema for a customer record: {"type": "object", "properties": {"name": {"type": "string"}, "age": {"type": "integer", "minimum": 0}}, "required": ["name"]}. A validator takes a schema and a candidate document and reports pass or fail (plus, in most implementations, a list of violations). Schemas compose: "items" describes the schema every array element must satisfy, and "$ref" lets one schema reuse another by reference, which is how large systems avoid duplicating the definition of, say, an address object across every schema that embeds one. Types and constraints Beyond bare type checks, JSON Schema layers on constraints per type. Strings get minLength, maxLength, pattern (a regular expression), and format (semantic hints like email or date-time, which validators may or may not enforce strictly). Numbers get minimum, maximum, exclusiveMinimum/exclusiveMaximum, and multipleOf. Arrays get minItems, maxItems, uniqueItems, and per-element schemas via items. Objects get required, additionalProperties (set to false to reject any field not explicitly listed in properties, catching typos and unexpected extra data), and patternProperties for field names that follow a naming pattern rather than a fixed list. Combinators, namely allOf, anyOf, oneOf, and not, let a schema express "must match all of these," "at least one," "exactly one," or "must not match." Error messages A validator that only says "invalid" is far less useful than one that reports which path failed and why. Good validation output includes the JSON pointer to the offending location (e.g. /orders/2/qty), the keyword that failed (minimum, required, type), and the expected versus actual value. When designing APIs or config loaders around JSON Schema, surface these details to the caller rather than collapsing every failure into a generic 400 error: a missing required field and a wrong type are different bugs and deserve different messages. Evolution Documents outlive schemas. Adding an optional field is safe and backward compatible; adding a required field breaks every existing document that predates it. Strategies for evolving schemas gracefully: version the schema explicitly (a schema_version field in the document, or a versioned $id), prefer additive optional fields over required ones, use oneOf to accept either an old shape or a new shape during a migration window, and avoid additionalProperties: false on documents you expect to grow, since it turns every future field addition into a breaking validation change for old validators. The safest long-term pattern combines several of these strategies at once: stamp every document with an explicit schema_version, validate new writes against the current schema version while tolerating documents written under a previous version on read, and only remove support for an old version once a migration has actually run against the stored data, rather than assuming validation alone will keep every document current. Treating schema evolution as an ongoing operational process, with its own versioning and migration discipline, rather than a one-time design decision, is what keeps a document store usable years after the first schema was written. Related CCI capabilities JSON Schema validation is the same discipline as software input validation applied to documents: reject the malformed, the unexpected, and the malicious before it reaches application logic. CCI's Secure Software Development Life Cycle (SDLC) at Scale engagement (https://www.cambridgecyberinternational.com/en/services/secure-sdlc/) builds schema and input validation checks into the lifecycle itself, so a missing "required" field or an overly permissive additionalProperties setting is caught in design and review rather than discovered after a breach.